Help needed with Median If in Excel

user_852684 0
Viewed 124452

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!

median if-statement excel function
4 Answers

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".

user_70166330
Related