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.
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:
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})
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.
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.
Advantages of user-defined functions
We love UDFs, so we recommend you check our function library, DataFX. 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.
Additional resources:
