Help needed with Median If in Excel

Question

I need to return a median of only a certain category on a spreadsheet. Example Below:

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

etc.

How can I create a formula that just returns the airline categories' median value? like Average if, but only for median. I am unable to change the values.

Answer 1

Try entering the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and clicking CTRL+SHIFT+ENTER if your categories are in cells A1:A6 and the associated values are in cells B1:B6.

Excel is instructed to treat the formula as an "array formula" when CTRL+SHIFT+ENTER is used. This means that instead of returning a single value in this case, the IF statement instead produces an array of 6 values (one for each cell in the range $A$1:$A$6). The median of these numbers is then returned by the MEDIAN function.