How to Calculate MEDIAN IF in Excel (“MEDIANIFS” Formula)
Excel is a powerful tool that can help you easily analyze and manipulate data.
One of the most useful functions in Excel is the median function, which helps find the middle value in a set of data.
However, what if you only want to find the median of a subset of data that meets certain criteria? 🤔
This is where the “MEDIAN IF” function comes in handy!
In this article, we will explore how to calculate the median in Excel. And how it can be used to calculate conditional medians, with the help of our sample workbook here.
MEDIAN IF in Excel
The MEDIAN IF function in Excel is used to find the median value from a data set.
It refers to the middle number, and this function can be incredibly useful in analyzing a large data set 🧐
Its syntax is given as follows:
=MEDIAN(IF(range, criteria, [range2], [criteria2], …))
- range: The range of cells that you want to evaluate.
- criteria: The criteria that you want to use to evaluate the cells in the range. This can be a value, a cell reference, or an expression that evaluates to a value or reference.
- [range2], [criteria2], …: additional ranges and criteria to further filter data
Note that the MEDIAN IF function is an array formula. So you must enter it as an array formula by pressing Ctrl+Shift+Enter, rather than just Enter, to finish the formula.
Let’s see a quick example of how to use the MEDIAN IF function in Excel 😃
Say, we have the following sample data.
We want to calculate the median price for all fruits.
To do that,
- Select cell D2.
- Enter the MEDIAN IF formula as:
=MEDIAN(IF(
- Enter the range.
=MEDIAN(IF(B2:B9=
- Now add the criteria as to what we want to check.
=MEDIAN(IF(B2:B9=”Fruit”,
- Enter the column containing prices.
=MEDIAN(IF(B2:B9, “Fruit”, C2:C9)
- Press CTRL + Shift + Enter to enclose the array formula.
The MEDIAN IF function returns the result of $1.375, which is the median price of all fruits in the data set.
Let’s now understand how the array formula determines this result.
First, the IF function evaluates the range of cells in the “Category” column. And checks which cells match the specified criteria (“Fruit” in this case) 🥭
The IF function returns an array of TRUE/FALSE values. Here each element of the array corresponds to a cell in the “Category” column.
The elements of the Category that correspond to “Fruit” will be TRUE, and the rest will be FALSE ❌
The MEDIAN function then takes this array of TRUE/FALSE values. And uses it to determine which cells in the “Price” column to include in the median calculation.
It only includes the prices for items that are in the “Fruit” category. The MEDIAN function then calculates the median of this subset of prices, which is $1.375 💲
So, in essence, the formula is telling Excel to only include prices for items that are in the “Fruit” category. And to ignore prices for items in other categories when calculating the median.
The result is the median price for fruits in the dataset.
Pretty cool, no? Try it yourself now! 🤩
That’s it – Now what?
In this article, we saw how to calculate MEDIAN IF in Excel. By using the MEDIAN IF function, you can easily find the median of a data set that meets certain conditions.
This can be incredibly useful when analyzing large data sets or looking for specific insights within your data. 💻
Using this guide, you’ll be able to quickly calculate conditional medians in Excel. All the while allowing you to unlock even more valuable insights from your data.
So go ahead and try out the MEDIAN IF Excel function for yourself. And see how it can help you make better, data-driven decisions!
Other resources
Did you enjoy reading this article? If yes, then you’d love to know more.
Try similar topics: Find Median in Excel, Find Average, Use the IF function, and more.