Help needed with Median If in Excel

Alan picture Alan · Jul 19, 2011 · Viewed 114.1k times · Source

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!

Answer

Brian Camire picture Brian Camire · Jul 19, 2011

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.