If you have statistical data and need to find out the median value, Excel has a MEDIAN function built-in for this purpose. Using just single formula, you can calculate the middle value within a few seconds. Similarly, if you want to evaluate the median based on a condition, you could also nest the MEDIAN function with the IF function.
Use MEDIAN Function
The MEDIAN function returns the middle value from the statistical data. But, for an even number, it returns the average between two middle values. It’s just like how you used a different formula to calculate the Median in Maths. For Instance, (n+1/2)th item formula for odd numbers and (n/2)th item for even numbers.
Note that the formula we use for odd and even numbers is the same in the MEDIAN function. But, the output you get is different as we mentioned above. Here, the function will look at the total number of values (n) for an even or odd number.
This function is easy to use and understand. Also, you do not have to sort the value in ascending order to calculate the median.
Syntax: MEDIAN(number1, [number 2], ….)Now, let’s check out these examples for more detail.
Example 1: Odd Numbers
To calculate the median of Odd Numbers, we used the following formula.
=MEDIAN(C2:C8)
In our data, the number (n) is 7 which is an odd number. So, the formula returns 70 as an output.
Example 2: Even Numbers
Say you have an even number (N) in the data. To find out the middle value, we entered the Median formula as
=MEDIAN(C2:C9)
The formula returns 69.5 as a result because the number(n) is 8 in our data.
Use Nested MEDIAN Function
Suppose you need to calculate the middle numbers from data based on a specific condition. For this, Excel does not have a default MEDIANIF function like AVERAGEIF. But, you can still nest the MEDIAN and IF functions to find out the conditional median.
Before we begin, let’s see the Syntax and arguments of the IF function first.
Syntax: IF(logical_test, value_if_true, [value_if_false])- Logical_test: Condition to test in your data. [required]
- Value_if_true: Returned Value when the logical_test is TRUE. [required]
- Value_if_false: Returned Value when the logical_test is FALSE. [optional]
Example 1: IF(MEDIAN, Value_if_true, Value_if_false)
In example 1, we will nest the MEDIAN function inside the IF function. The formula will return the Median value first. Then, the IF function will take MEDIAN as logical_test and return a True or False value.
Let’s see the application of this formula. Here, we will compare the English test scores of students with the median value. Let’s consider the students who scored higher than the median as “Good” and lesser as “Poor.”
For this, we entered the formula as:
=IF(C2>=MEDIAN($C$2:$C$9), "Good", "Poor")
Extend the flash-fill icon to rewrite the formula for other cells too. You can check out how the formula works.
- Firstly, the =MEDIAN($C$2:$C$9) formula returns the Median value of 69.5.
- Then, the IF function takes the function arguments as below.
- Logical_test: (C2>=MEDIAN($C$2:$C$9)
- Value_if_true arguments: Good
- Value_if_flase argument: Poor
Meaning, if the value of the C2 score is greater than the median 69.5, it’ll return Good. If it is False, you’ll get Poor.
Example 2: MEDIAN(IF(logical_test, value_if_true, value_if_false))
You can also nest the IF function inside the MEDIAN function. Here, the formula will first extract the condition value from the data. Then, calculates the middle value of the returned output.
In the given case, we have lists of months and total sales in data. Here, we want to calculate the median based on the month. So, we used the following formula.
=MEDIAN(IF(A2:A20 = D2, B2:B20))
- Firstly, the formula will return the value of IF function. Here, the IF function takes (A2:A20 = D2) as logical_test and B2:B20 as Value_if_true arguments. So, if the condition is True, the formula will return only the value of April month in our case.
- Then, the MEDIAN function looks for the median value in the April months and returns 48.6 as the final result.