Help needed with Median If in Excel
Solution 1
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
.
Solution 2
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.
Solution 3
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".
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
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!
-
Piotrek about 7 yearsCan 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
-
Przemyslaw Remin almost 5 years@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.