Learn how to use the MEDIAN IF formula in Excel to identify the middle number of values that meet one or more specific criteria.
What is a median?
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, the median is worth preferring if your data set contains peaks. You can construct formulas using the built-in MEDIAN function to calculate the median in Excel. What if you want to use one or more conditions?
How to calculate MEDIAN IF
The generic formula is (using built-in Excel functions):
=MEDIAN(IF(range, criteria, [range2], [criteria2], …))
- Range: The group of cells you want to analyze.
- Criteria: The condition you want to apply to evaluate the cells in the specified range. This could be a number, a reference to a cell, or an expression that returns a value or cell reference.
- [range2], [criteria2], …: Additional ranges and conditions to refine your data further.
Let’s see an example of how to use the MEDIAN IF function in Excel: In this case, you’re calculating the median values for specific groups (A or B).
Formula:
=MEDIAN(IF(group=E3, data))
The formula allows you to calculate the median of a subset of data that meets specific criteria. In the example, I’m using two named ranges: B3:B13 for “group” and C3:C13 for “data”.
Evaluate the formula:
- MEDIAN: This is the core function, which calculates the median of a set of numbers. If there are even numbers of values, the value is the average of the two middle numbers.
- IF(group=E3, data): This part is an array operation. The IF function creates a condition in which the values in the “data” range are included in the calculation only if the corresponding value in the “group” range matches the value in E3. In this case, the groups are A and B. The formula checks which rows have a value of A or B in the “Group” column and then selects the corresponding numbers in the “Value” column for the median calculation.
- group=E3: This is the condition applied within the IF function. It checks whether each value in the “group” range matches the value in cell E3 (either “A” or “B”). If a match is found, the corresponding value from the “data” (or “Value”) column is included in the calculation. For E3, the group is A, so the formula will return the values from the “Value” column where the “Group” is A.
In F3, the formula calculates the median of values associated with group A. The corresponding values in the “Value” column for group A are 10, 40, 20, 80, and 70. When these values are sorted (10, 20, 40, 70, 80), the median is 55, as it is the middle number.
In F4, the formula calculates the median for group B. The values associated with group B are 30, 70, 40, and 20. When sorted (20, 30, 40, 70), the median is 30 because it is the average of the two middle values (30 and 40).
Formula based on a user-defined function
You can use a workaround by combining standalone MEDIAN and IF functions or applying a user-defined function to create easily readable formulas.
The MEDIANIF 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
MEDIANIF is a part of our free function library.
In the example, we find the median and use the 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. 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})
How to calculate the median with multiple criteria?
In this example, we will show you how to calculate the 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
The next example will demonstrate how to calculate 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.
It’s good to know that when 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))}
Conclusion
In this article, we explored how to calculate MEDIAN IF in Excel. You can easily determine the median of a dataset that meets specific conditions. This is especially helpful when working with large datasets.