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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt  gt     B 1  B 6      Don t forget to press CTRL SHIFT ENTER to treat the formula as an  array formula

[excel] Help needed with Median If in Excel

Examples related to excel

Examples related to function

Examples related to if-statement

Examples related to median