Median If Formula – Excel & Google Sheets

This tutorial will demonstrate how to calculate the median value that meets certain criteria in Excel and Google Sheets.

 

MEDIAN Function

The MEDIAN Function calculates the median value.

=MEDIAN(C2:C10)

However, there is no built-in “Median If” Function to calculate the median value only for numbers that meet certain criteria.

Note: You might be familiar with the AVERAGEIFS Function that will calculate the average for numbers that meet certain criteria. There is no median alternative.

Median If – Array Formula

So, to calculate “Median If”, we must use an Array Formula with the IF Function inside the MEDIAN Function:

=MEDIAN(IF($A$2:$A$10=$E2, $C$2:$C$10))

Let’s walk through this formula…

Excel 2019 and Earlier

In Office 365 and versions of Excel after 2019, you can simply enter the above formula like you normally would (by pressing ENTER).

However, for Excel 2019 and earlier you must enter the formula by pressing CTRL + SHIFT + ENTER. After doing so, you’ll notice curly brackets appear around the formula:

{=MEDIAN(IF($A$2:$A$10=$E2, $C$2:$C$10))}

Important: do not write these curly brackets yourself, they must be entered only with CTRL + SHIFT + ENTER.

How does the formula work?

Remember this is our formula:

{=MEDIAN(IF($A$2:$A$10=$E2, $C$2:$C$10))}

The formula works be evaluating each value’s criteria as TRUE or FALSE.

=MEDIAN(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE},{1287;1205;1243;1482;1261;1042;1090;1748;1909}))

The IF Function replaces TRUE with the actual values.

=MEDIAN({FALSE;1205;FALSE;FALSE;1261;FALSE;1090;1748;FALSE})

Now the MEDIAN Function ignores FALSE values and calculates the median of the remaining values (1233 is the average of the two middle values: 1205 and 1261).

Median IF – Multiple Criteria

You can also calculate a median based on multiple criteria by using Boolean Logic.

Let’s take a look at a new data set with columns Location, Year, and Stock:

Now here’s the formula to calculate the median based on multiple criteria:

=MEDIAN(IF((A2:A4="B")*(B2:B4=2008),C2:C4))

Notice here we multiply two sets of criteria together:

(A2:A4="b")*(B2:B4=2008)

If both criteria are TRUE then it will calculate as TRUE, but if one (or more) criteria is FALSE it will calculate as FALSE.

Median If Formula in Google Sheets

All of the above examples work the same in Google Sheets, except your formula must be contained inside the ARRAYFORMULA Function (because it’s an array formula).

=ARRAYFORMULA(MEDIAN(IF($A$2:$A$10=$E2, $C$2:$C$10)))