Excel is a versatile software to organize, analyze, and visualize data efficiently and, in turn, foster informed decision-making. The Median function comes in handy to find out the middle value of a dataset.
But what if you want to determine it for a subset of data that meets certain criteria? Here comes “MEDIAN IF”. If you wish to be adept at it, you’re at the right place. In this article, you’ll learn everything about it.
So, without further ado, let’s dive in!
Steps To Use Median If in Excel
Excel doesn’t provide any special function to calculate the Median based on a special condition, like for the arithmetic mean (AVERAGEIF and AVERAGEIFS functions). Luckily, you can create your own MEDIAN IF formula.
Here’s how it looks:
=MEDIAN(IF(range, criteria, [range2], [criteria2],..))
Where,
- range= The range of cells you want to evaluate.
- criteria= The criteria to use to evaluate the cells in the range. It can be a value, expression, or cell reference.
- [range2], [criteria2],= additional range or criteria you want to apply.
Note: With the MEDIAN IF function, you must enter Ctrl + Shift + Enter rather than just Enter to finish the formula. Let’s understand it with a real-time example.
Let’s say I have the following sample data:
Here I want to calculate the median price of all vegetables.
- Select an empty cell adjacent to the dataset and enter the formula as =MEDIAN(IF(
- Enter the range like C2:C10, or just select the cells manually. Anything works!
- As per the formula, put the condition next, here it’s if the “Item is a vegetable.” So continue the formula as, =MEDIAN(IF(C2:C10= “Vegetable”.
- Then select the cells containing the prices. The complete formula should look like this: =MEDIAN(IF(C2:C10= “Vegetable”, D2:D10)
- Press Ctrl + Shift + Enter to enclose the array formula. The MEDIAN IF function yields a value of 1.665, which is the middle data or median price of all the vegetables in the dataset.
How Does Median If Formula Work?
Now that you figured out how to use the formula, it’d be handy to understand how it actually works.
As you enter the formula and press Ctrl + Shift + Enter, the IF function evaluates the range of cells included in the “category” column, followed by the cells that match the provided criteria.
In my stated example, it’s “Vegetable.” So, for the cells that match the “value/criteria,” Excel considers it TRUE and the rest FALSE.
Then, it determines the Price cells adjacent to the ones that match the provided criteria or are deemed as TRUE and calculate the Median of the respective values. It ignores the rest!
Pretty cool, right? Try yourself now!
Using MEDIAN IF for Multiple Criteria
In case you want Excel to calculate the Median with multiple criteria, the formula is framed as follows:
=MEDIAN(IF(range=”criteria”)*(range2=”criteria), range 3))
Let’s understand it by an example:
- Let’s consider a new dataset with columns Location, Year, and Stock:
- Here’s the complete formula when you want to calculate the median of stocks at Location B in the year 2008. =MEDIAN(IF(A2:A7=”B”, B2:B7=2008, C2:C7))
- Then press Ctrl + Shift + Enter. The obtained Median value of selected cells is 3140.
Using MEDIAN IF for Grouped Data
Consider the following dataset:
| Height (in cm) | Number of Boys |
|---|---|
| Less than 140 | 4 |
| Less than 145 | 12 |
| Less than 150 | 25 |
| Less than 160 | 36 |
| Less than 165 | 48 |
| Less than 170 | 55 |
To calculate the MEDIAN IF of the dataset, first, we need to rearrange the table as follows:
| Lower Limit | Upper Limit | Frequency |
|---|---|---|
| 140 | 144 | 4 |
| 145 | 149 | 12 |
| 150 | 154 | 25 |
| 155 | 159 | 36 |
| 160 | 164 | 48 |
| 165 | 169 | 55 |
| 170 | 174 | 4 |
Suppose in the above table, I want to calculate the median of data of boys below the height of 154cm. The formula should go as follows:
=MEDIAN(IF(B2:B8>154, C2:C8))
Then press Ctrl + Shift + Enter, which yields a value of 42.
Besides finding the median, you can perform various calculations in Excel such as finding the interquartile range, squaring a number, and subtracting columns.
Frequently Asked Questions
Follow the below steps in order:
1. Make sure you’ve put in a correct formula, and press Ctrl + Shift + Enter after typing the formula, as it’s an Array formula.
2. Double-check the data range you’ve put in.
3. Ensure the data in your specified range is in correct type. The formula might not work properly if any non-numerical values are present.
4. Test your conditions separately to ensure it produces the right results. This helps determine whether the issue is with the function or not.
5. Errors in your data might cause #VALUE! Or #DIV/0! Errors.
When you use the median calculation on an even number of values in a range, the median is the average of the two middle values.
Wrap Up!
With that comes an end to my guide. MEDIAN IF comes in handy when dealing with large datasets or finding specific insights within them. The formula isn’t complicated, but ensure range, criteria, and other components are put in right to avoid any Error.
I hope this helps. I’ll see you in another such informational guide. Till then, goodbye and take care!
Leave a Reply