Median with multiple criteria

[Eslava]

I've been trying to get a formula that will calculate a median for base salary if it equals two criteria:
Grade: 15-8 (in column C)
Department: E or S (in column O)
the base salaries are in column I

I put the E's in Column A, S's in Column B, and the Grades in Column C of the worksheet where i need the median.

I tried the following: =MEDIAN(IF((data!$O$3:$O$200=$B10)+(data!$C$3:$C$200=$C10),data!$I$3:$I$200))

because it is such a large data set, I don't want to go in manually and calculate every median for the specific sets.

Is there a way?

 

[me_hahaha]

lol my bad, I was too tired last night to look at the date.
Thanks for the help thou...it works partially...I used
=MEDIAN(IF(Template!$N$1:$N$40000<>"#N/A",IF(Template!$N$1:$N$40000<>"Surcharge",IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000))))
it returns #N/A
If I am to take out IF(Template!$A$1:$A$40000=Report!$A$3) ...it works or if I change Template!$A$1:$A$40000 with Template!$B$1:$B$40000 (subset of A but with written text) it works...I'm pretty sure it's the data from Template!$A$1:$A$40000 - column A from Template uses vlookup formula to get data from a third spreadsheet

 

[joeu2004]

I used
=MEDIAN(IF(Template!$N$1:$N$40000<>"#N/A",
IF(Template!$N$1:$N$40000<>"Surcharge",
IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000))))
it returns #N/A
If I am to take out IF(Template!$A$1:$A$40000=Report!$A$3) ...it works or if I change Template!$A$1:$A$40000 with Template!$B$1:$B$40000 (subset of A but with written text) it works...I'm pretty sure it's the data from Template!$A$1:$A$40000 - column A from Template uses vlookup formula to get data from a third spreadsheet

It appears that the value of some cells in the range Template!$A$1:$A$40000 is the Excel error #N/A (not the text "#N/A").

If everything else in the MEDIAN expression seems to work, change it as follows (again, array-enter by pressing ctrl+shift+Enter instead of just Enter):

=MEDIAN(IF(Template!$N$1:$N$40000<>"#N/A",
IF(Template!$N$1:$N$40000<>"Surcharge",
IF(ISERROR(Template!$A$1:$A$40000)=FALSE,
IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000)))))

However, as Aladin points out, the first condition -- Template!$N$1:$N$40000<>"#N/A" - is suspicious. Is "#N/A" truly text, or is it the Excel error #N/A?

Find one such cell in Template!$N$1:$N$40000 (e.g. N100) and see what =ISNA(N100) returns. If TRUE, change the formula above as follows (remember: press ctrl+shift+Enter):

=MEDIAN(IF(ISNA(Template!$N$1:$N$40000)=FALSE,
IF(Template!$N$1:$N$40000<>"Surcharge",
IF(ISERROR(Template!$A$1:$A$40000)=FALSE,
IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000)))))

 

[Aladin Akyurek]

lol my bad, I was too tired last night to look at the date.
Thanks for the help thou...it works partially...I used
=MEDIAN(IF(Template!$N$1:$N$40000<>"#N/A",IF(Template!$N$1:$N$40000<>"Surcharge",IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000))))
it returns #N/A
If I am to take out IF(Template!$A$1:$A$40000=Report!$A$3) ...it works or if I change Template!$A$1:$A$40000 with Template!$B$1:$B$40000 (subset of A but with written text) it works...I'm pretty sure it's the data from Template!$A$1:$A$40000 - column A from Template uses vlookup formula to get data from a third spreadsheet

Did you see post #20?

 

[me_hahaha]

it works...I guess it was an error #N/A
I used =MEDIAN(IF(Template!$N$1:$N$40000<>"#N/A",
IF(Template!$N$1:$N$40000<>"Surcharge",
IF(ISERROR(Template!$A$1:$A$40000)=FALSE,
IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000))))).
Thanks guys for all the help...with Excel you learn new things everyday Have a great week!

 

[Aladin Akyurek]

it works...I guess it was an error #N/A
I used =MEDIAN(IF(Template!$N$1:$N$40000<>"#N/A",
IF(Template!$N$1:$N$40000<>"Surcharge",
IF(ISERROR(Template!$A$1:$A$40000)=FALSE,
IF(Template!$A$1:$A$40000=Report!$A$3,Template!$N$1:$N$40000))))).
Thanks guys for all the help...with Excel you learn new things everyday Have a great week!

Try post #20 though...

 

[me_hahaha]

I tried =MEDIAN(IF(ISNUMBER(Template!$N$2:$N$40000),IF(Template!$A$2:$A$40000=Report!$A3,Template!$N$2:$N$40000))) and it comes up with #N/A...the error I think

 

[Aladin Akyurek]

I tried =MEDIAN(IF(ISNUMBER(Template!$N$2:$N$40000),IF(Template!$A$2:$A$40000=Report!$A3,Template!$N$2:$N$40000))) and it comes up with #N/A...the error I think

It should not, unless you also have error values (e.g., #N/A) in column A of Template.

Otherwise:

{=MEDIAN(IF(ISNUMBER(Template!$N$2:$N$40000),IF(1-ISERROR(Template!$A$2:$A$40000),IF(Template!$A$2:$A$40000=Report!$A3,Template!$N$2:$N$40000))))}

 

[me_hahaha]

Yes, you are right, I had #N/A in column A of Template...and it works

{=MEDIAN(IF(ISNUMBER(Template!$N$2:$N$40000),IF(1-ISERROR(Template!$A$2:$A$40000),IF(Template!$A$2:$A$40000=Report!$A3,Template!$N$2:$N$40000))))}

Thank you for all the help

 

[Aladin Akyurek]

Yes, you are right, I had #N/A in column A of Template...and it works

{=MEDIAN(IF(ISNUMBER(Template!$N$2:$N$40000),IF(1-ISERROR(Template!$A$2:$A$40000),IF(Template!$A$2:$A$40000=Report!$A3,Template!$N$2:$N$40000))))}

Thank you for all the help

You are welcome. I kept insisting also because testing the median range (numeric range) for <>"Surcharge" cannot contribute to the solution.