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?

 

[Domenic]

Ignore Column E. Column G will contain the medians of the E's and Column H will have the S's, all according to the appropriate grade.

Then the first formula I offered, where I replaced '+' with '*', should return the desired result. Make sure that the numbers in Column C and I are being recognized as numerical values. For example, the following formula should return TRUE...

=ISNUMBER(C2)

Also, make sure that the values in Column O don't contain extra spaces.

 

[Eslava]

Both return a #NUM!

I tried this formula to get the median for the E's:
=MEDIAN(IF(((data!$O$3:$O$200=$A10)+(data!$C$3:$C$200=$C10)),data!$I$3:$I$200))

and I got 118,600 for Grade level 15, which is incorrect

and this formula for the S's:
=MEDIAN(IF(((data!$O$3:$O$200=$B10)+(data!$C$3:$C$200=$C10)),data!$I$3:$I$200))

and that came out to 105,600 for Grade level 15, also incorrect

Any suggestions?

 

[Eslava]

BTW, your original formula returns 105,600 for Grade level 15 for department S as well

 

[Eslava]

=MEDIAN(IF(data!$C$3:$C$200=$C10,IF(ISNUMBER(MATCH(data!$O$3:$O$200,$A10:$B10,0)),data!$I$3:$I$200)))

This gets me the correct median of the entire grade, but not the medians of the separate departments E and S

 

[Domenic]

Both return a #NUM!

I tried this formula to get the median for the E's:
=MEDIAN(IF(((data!$O$3:$O$200=$A10)+(data!$C$3:$C$200=$C10)),data!$I$3:$I$200))

and I got 118,600 for Grade level 15, which is incorrect

and this formula for the S's:
=MEDIAN(IF(((data!$O$3:$O$200=$B10)+(data!$C$3:$C$200=$C10)),data!$I$3:$I$200))

and that came out to 105,600 for Grade level 15, also incorrect

Any suggestions?

You'll need to be careful with your conditions. The first formula above states that you'd like the median base salary where the department is 'E" OR (not AND) the grade is '15'. Try...

=MEDIAN(IF((Data!$O$3:$O$200=$A10)*(Data!$C$3:$C$200=$C10),Data!$I$3:$I$200))

and

=MEDIAN(IF((Data!$O$3:$O$200=$B10)*(Data!$C$3:$C$200=$C10),Data!$I$3:$I$200))

Based on your sample worksheet, the first formula should return 230,000 and the second formula should return 207,500.

 

[Eslava]

Success! The format was the problem in the end. Thanks for all your efforts!

 

[Domenic]

Success! The format was the problem in the end. Thanks for all your efforts!

You're very welcome!

Cheers!

 

[me_hahaha]

Hi, I have a worksheet called Template, with 2 columns : Column A: Sales Region and Column N: Discount.
Under Discount: I have percentages between 0 and 100 and 2 texts: #N/A and Surcharge
Under Sales Region - names that are exactly the same with Column A from Report.
I'm trying to use this formula on another worksheet called "Report" to calculate the median from Template:
=MEDIAN(IF(AND(Template!N:N<>"#N/A",Template!N:N<>"Surcharge",Template!A:A=Report!$A3),Template!$N:$N))
it returns zero percent for every region. It doesn't work.
What am I doing wrong?

 

[joeu2004]

=MEDIAN(IF(AND(Template!N:N<>"#N/A",Template!N:N<>"Surcharge",
Template!A:A=Report!$A3),Template!$N:$N))
it returns zero percent for every region. It doesn't work. What am I doing wrong?

To begin with, posting a response to a 7-year-old thread instead of starting a new discussion.

But seriously....

First, I assume you know to array-enter the formula. That is, press ctrl+shift+Enter instead of just Enter.

Second, however, we usually cannot use AND() in an array-entered formula. It usually does not do what we intended. AND() itself "consumes" the entire array instead of processing it row-by-row, as I presume you intended.

Finally, it is ill-advised to use full-column range references like N:N and A:A. It usually causes Excel to process 1 million rows in Excel 2007 and later. And it usually causes Excel to create one or more 1-million-row temporary arrays, consuming far more memory than necessary. If you are unsure how many rows there might be, choose a reason limit. Most Excel workbooks do not have more than 10,000 or 100,000 rows.

In conclusion, array-enter the following formula (press ctrl+shift+Enter instead of just Enter), adjusting the ranges up or down as appropriate for your design.

=MEDIAN(IF(Template!$N$1:$N$1000<>"#N/A",
IF(Template!$N$1:$N$1000<>"Surcharge",
IF(Template!$A$1:$A$1000=Report!$A3,Template!$N$1:$N$1000))))

 

[Aladin Akyurek]

Hi, I have a worksheet called Template, with 2 columns : Column A: Sales Region and Column N: Discount.
Under Discount: I have percentages between 0 and 100 and 2 texts: #N/A and Surcharge
Under Sales Region - names that are exactly the same with Column A from Report.
I'm trying to use this formula on another worksheet called "Report" to calculate the median from Template:
=MEDIAN(IF(AND(Template!N:N<>"#N/A",Template!N:N<>"Surcharge",Template!A:A=Report!$A3),Template!$N:$N))
it returns zero percent for every region. It doesn't work.
What am I doing wrong?

1) #N/A is a special value in Excel, i.e., not a text value.

2) Try to avoid using whole columns for reasons of efficiency.

Control+shift+enter, not just enter:

=MEDIAN(IF(ISNUMBER(Template!$N$2:$N$1000),
  IF(Template!$A$2:$A$1000=Report!$A3,Template!$N$2:$N$1000)))