MEDIAN IF Formula

Learn how to use the MEDIAN IF formula in Excel to identify the middle number of values that meet one or more specific criteria.

The median is the middle number in a sorted list of numbers. We frequently use it because the average is not getting a comprehensive picture of the data set. However, if your data set contains peaks, it is worth preferring the median. You can use the built-in MEDIAN function to calculate the median in Excel. What if you want to use one or more conditions?

You can use a workaround with the combination of standalone MEDIAN and IF functions. Or you can use user-defined functions to create easy-to-readable formulas.

If you want to take a deep dive into Excel formulas, we recommend our definitive guide.

Generic formula to apply MEDIAN IF

The function uses two required arguments and has a simple syntax.

Syntax:

=MEDIANIF(range, criteria)

Arguments:

  • range: the range of cells that contains values
  • criteria: the condition that you want to use

In the example, we find the median and use the following condition: “value > 20.” Next, select the range B3:B13 and create a named range, “data“.

Formula:

=MEDIANIF(data, ">20")

Result:

medianif user defined function

The formula creates an array that contains numbers greater than 20.

Explanation:

Based on boolean logic, the formula will use only these values where the condition is TRUE.

={FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE}
={30, 70,  80, 40, 50, 70}
=MEDIAN({30, 70,  80, 40, 50, 70})
boolean logic array TRUE

MEDIAN IF – Multiple criteria

In this example, we will show you how to calculate a median of a data set if you have more criteria. The MEDIANIFS user-defined function enables you to use multiple range/criteria pairs like the normal IFS function. Unfortunately, Excel does not contain this function; download the practice file if you want to use it.

Syntax:

=MEDIANIFS(range, [range1,criteria1], [range2, criteria2]…..)

We aim to calculate the median using the data set that meets the following conditions.

  • criteria1: group = “A”
  • criteria2: value > 50

Formula:

=MEDIANIFS(data,range1,criteria1, range2, criteria2)
=MEDIANIFS(data,group,"=A",data,">50")

First, the formula finds and gets matching values where the Group =”A”.

={10, 40, 20, 80, 90, 70}

After that, the formula returns an array with values greater than 50. In this case, the return array contains three values:

={80, 90 70}

The second criteria filter the values, and the formulas return 80.

median if with multiple criteria

Array formula to calculate MEDIAN IF

The next example will demonstrate calculating the median if you want to use the built-in Excel functions. However, for the sake of simplicity, we will use the above-demonstrated data set.

Good to know when you are working with an array formula, you should use the Ctrl + Shift + Enter keys instead of a simple Enter.

The formula in F3:

{=MEDIAN(IF(group=E3,data))}

Copy the formula down to calculate the median for group B.

classic Excel array formula

Advantages of user-defined functions

We love UDFs, so we recommend you build your function library, like the DataFX add-in. Excel is great, but it has some limitations.

To implement a new function, you have two options: store the code in your Workbook or create a custom add-in that contains a reusable and portable collection.

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.