Help calculation MEDIAN

[Mark Mazza]

I am attempting to calculate MEDIAN when an item falls wthin a certain date range, the product is specific and the severity is specific. I have tried multipe MEDIAN formulas with no luck. I get NAME# for the following formula which I believe is due to the use of IFS but when I alter to 'IF' i get an edit 'YOU HAVE ENTERED TOO MANY ARGUMENTS FOR THIS FUNCTION'.

=MEDIAN(IFS('TTR Data'!$I:$I,'TTR Data'!$E:$E,">="&'Date Reference'!$C$3,'TTR Data'!$E:$E,"<="&'Date Reference'!$C$4,'TTR Data'!$N:$N,B5,'TTR Data'!$J:$J,$B$38))

NOTE: When I execute COUNTIFS and SUMIFS using the same formula and same reference data I get correct output (see below).

=COUNTIFS('TTR Data'!$E:$E,">=" & 'Date Reference'!$C$3,'TTR Data'!$E:$E,"<=" & 'Date Reference'!$C$4, 'TTR Data'!$N:$N,B5, 'TTR Data'!$J:$J,$B$38)
------above counts the # of SRS for the product closed in the month and at the specific severity and WORKS!!!!!---

=SUMIFS('TTR Data'!$I:$I,'TTR Data'!$E:$E,">="&'Date Reference'!$C$3,'TTR Data'!$E:$E,"<="&'Date Reference'!$C$4,'TTR Data'!$N:$N,B5,'TTR Data'!$J:$J,$B$38)
------above counts the houa to close all SRS for the product closed in the month and at the specific severity and WORKS!!!!!---

Sorry for long email but have been stuck for a day

Any help would be greatly appreciated

Mark

 

[SirJB7]

Hi, Mark Mazza!

At a first glance and not having checked the formulas against proper data (not provided by you and me being lazy as Friday), you're using a function that doesn't exist: IFS.
If you assimilated IFS to IF as COUNTIFS to COUNTIF and SUMIFS to SUMIF, you're making a mistake, IF has the following format:
=IF(<condition>,<value_if_true>,<value_if_false>)

If I were you, I'd try 2 things and if none worked then I'd post the complete set of sample files with proper sample data to check the issue:
a) Replace IFS by IF
b) Replace IFS by IF and enter it as an array formula (press Ctrl-Shift-Enter instead of Enter)

Hope it helps.

Regards!

 

[NARAYANK991]

Hi Mark ,

Can you try either of the following :

=MEDIAN(IF(('TTR Data'!$E:$E>='Date Reference'!$C$3)*('TTR Data'!$E:$E<='Date Reference'!$C$4)*('TTR Data'!$N:$N=B5)*('TTR Data'!$J:$J=$B$38),'TTR Data'!$I:$I))

or

=MEDIAN(IF('TTR Data'!$E:$E>='Date Reference'!$C$3,IF('TTR Data'!$E:$E<='Date Reference'!$C$4,IF('TTR Data'!$N:$N=B5,IF('TTR Data'!$J:$J=$B$38,'TTR Data'!$I:$I)))))

Both are to be entered as array formulae , using CTRL SHIFT ENTER.

Narayan

 

[Mark Mazza]

Narayan

The second formula appears to be working and I am working to incorporate into my spreadsheet. Is there a way to do the array formula, CTRL SHIFT ENTER, on a large # of cells or do you have to access each one?

Apologies, I am unable to post my data as it contains confidential information and would take too long to eliminate this data. I knew/know this would have made it easier.

Mark

 

[NARAYANK991]

Hi Mark ,

Sorry , but it's late tonight , and I will not be able to check this out ; hope some else can help out today.

Narayan

 

[SirJB7]

Hi, Mark Mazza!
You can copy and paste array formula cell's normally, if your target is an empty range or a range with no previous array formulas. If this last is the case, first delete them and then copy as usual.
Regards!

 

[Mark Mazza]

Thank you SirJB7! Appreciate the direction