Help needed with Median If in Excel

Question

I need to return a median of only a certain category on a spread sheet. Example Below

Airline    5
Auto       20
Auto       3
Bike       12
Airline    12
Airline    39

ect.

How can I write a formula to only return a median value of the Airline Categories. Similar to Average if, only for median. I cannot re-arrange the values. Thank you!

This looks like a duplicate of [this][1] [1]: http://stackoverflow.com/questions/742547/conditional-median-in-ms-excel — yosh m, Nov 13 '11 at 17:17
This looks like a duplicate of [this][1] [1]: http://stackoverflow.com/questions/742547/conditional-median-in-ms-excel — yosh m, Nov 13 '11 at 17:19

score 26 · Answer 1 · edited May 08 '18 at 21:48

26

Assuming your categories are in cells A1:A6 and the corresponding values are in B1:B6, you might try typing the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and then pressing CTRL+SHIFT+ENTER.

Using CTRL+SHIFT+ENTER tells Excel to treat the formula as an "array formula". In this example, that means that the IF statement returns an array of 6 values (one of each of the cells in the range $A$1:$A$6) instead of a single value. The MEDIAN function then returns the median of these values. See http://www.cpearson.com/excel/arrayformulas.aspx for a similar example using AVERAGE instead of MEDIAN.

edited May 08 '18 at 21:48

Community

1
1

answered Jul 19 '11 at 19:22

Brian Camire

4,685
2
26
23

Can you tell me what CTRL+SHIFT+ENTER does here? I get different values if I press this shortcut and if I don't. I'm trying to learn which value is correct – Piotrek Mar 18 '17 at 17:25
@Piotrek change for a short while the formula from MEDIAN to SUM and you will understand how it works. The shortcut shift+cntr+enter makes the IF work. If you do not press it then it calculates the formula for entire range. – Przemyslaw Remin May 16 '19 at 08:31

score 3 · Answer 2 · answered Sep 16 '19 at 04:45

Expanding on Brian Camire's Answer:

Using =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) with CTRL+SHIFT+ENTER will include blank cells in the calculation. Blank cells will be evaluated as 0 which results in a lower median value. The same is true if using the average funtion. If you don't want to include blank cells in the calculation, use a nested if statement like so:

=MEDIAN(IF($A$1:$A$6="Airline",IF($B$1:$B$6<>"",$B$1:$B$6)))

Don't forget to press CTRL+SHIFT+ENTER to treat the formula as an "array formula".

score 3 · Answer 3 · answered Jul 19 '11 at 19:23

3

Make a third column that has values like:

=IF(A1="Airline",B1)

=IF(A2="Airline",B2) etc

Then just perform a median on the new column.

answered Jul 19 '11 at 19:23

heisenberg

9,665
1
30
38

score 0 · Answer 4 · answered Jul 19 '11 at 19:14

0

one solution could be to find a way of pulling the numbers from the string and placing them in a column of just numbers the using the =MEDIAN() function giving the new number column as the range

answered Jul 19 '11 at 19:14

jth41

3,808
9
59
109

Help needed with Median If in Excel

4 Answers4

Linked