The median of an odd number of numeric values is the middle value of the numbers arranged in an ascending or descending order. If the number of the values is even, the median is the average of the two middle numbers.
You can use Excel’s built-in MEDIAN function to calculate the median of an array of numbers. However, Excel does not have a built-in function to find MEDIAN IF, a conditional median, the median of a list of numbers based on a specified condition(s).
As I will show you in this tutorial, you have to use workaround formulas to calculate a conditional median in Excel.
Method #1: Using IF and MEDIAN Functions
You can use an array formula wrapping the IF function in the MEDIAN function to calculate the median in Excel.
The IF function checks whether a condition is met, returning one value if the condition is met and an alternative value if the condition is not met.
Suppose you have the price list below for desktops, laptops, and tablets, and you want to calculate only the median price of desktop computers.
You can compute the desired median price using the formula below:
=MEDIAN(IF(B2:B16="Desktops",C2:C16))
The above formula uses the IF function to check each cell in the range B2:B16 if it equals ‘Desktops.’
For each cell in the range that equals ‘Desktops,’ the IF function returns the corresponding value in the cell range C2:C16. The MEDIAN function calculates the median of the resultant array of values.
Note: If you have an older version of Excel, press Ctrl + Shift + Enter to enter the formula as an array formula. If you have Excel 365 and later, you only need to press Enter.
Check For Multiple Conditions
You can check for multiple conditions inside the IF function. For instance, you can use the formula below to find the median price of only those tablets costing more than $180:
=MEDIAN(IF((B2:B16="Tablets")*(C2:C16>180),C2:C16))
Here’s an explanation of the formula:
- (B2:B16=”Tablets”) – This part of the formula returns TRUE if a cell in the cell range B2:B16 contains ‘Tablets’ and FALSE if it contains any other value. Let’s call the resultant array of values ‘Array1.’
- (C2:C16>180) – This part of the formula returns TRUE if a cell in C2:C16 contains a value greater than 180 and FALSE if it contains a value less than 180. Let’s call the resultant array of values ‘Array2.’
- The multiplication (*) between the ‘Array1’ and ‘Array2’ works as an AND operator. It results in an array where a value is TRUE only if both conditions are met for the corresponding row. Let’s call the resultant array of values ‘Array3.’
- The IF function returns the values in the cell range C2:C16 where the corresponding entries in ‘Array3’ are TRUE and ignores all values where the corresponding value in ‘Array3’ is FALSE. Let’s call the array of values generated from this process ‘Array4.’
- The MEDIAN function returns the median of the values in ‘Array4,’ the median price of tablets costing more than $180.
Also read: BETWEEN Formula in Excel
Method #2: Using MEDIAN and FILTER Function
You can use an array formula wrapping the FILTER function in the MEDIAN function to calculate the median in Excel. The FILTER function filters a range of data based on criteria you set and is only available in Excel 365 and later.
The syntax of the FILTER function:
FILTER(array, include, [if_empty])
The ‘array’ argument is required and is the range to filter.
The ‘include’ argument is required and is the array of TRUE/ FALSE values. It must be the same height or width as the ‘array.’
The ‘if_empty’ argument is optional and is the value to be returned if the filter returns nothing.
Suppose you have the price list below for desktops, laptops, and tablets, and you only want to calculate the median price of desktop computers.
You can compute the desired median using the formula below:
=MEDIAN(FILTER(C2:C16,B2:B16="Desktops"))
In the above formula, the FILTER function returns values in the cell range C2:C16 where the corresponding value in B2:B16 is ‘Desktops.’
The MEDIAN function calculates the median of the resultant array of values.
Filter on Multiple Conditions
You can use the FILTER function to filter on multiple conditions.
For example, you can use the formula below to find the median price of only those tablets costing more than $180:
=MEDIAN(FILTER(C2:C16, (B2:B16 = "Tablets") * (C2:C16 > 180)))
Explanation of the Formula
The formula above calculates the median of a filtered range of prices based on two conditions. Here’s how it does it:
- C2:C16 is the array to filter. It contains the prices from which we want to calculate the median.
- (B2:B16 = “Tablets”) * (C2:C16 > 180) represents the ‘include’ argument. It is the condition to filter the data and is arrived at in the following way:
The (B2:B16 = “Tablets”) part checks each cell in the range B2:B16 to see if it equals ‘Tablets.’ It returns an array of TRUE or FALSE values.
The (C2:C16 > 180) segment checks each cell in the range C2:C16 to see if it is greater than 180. It also returns an array of TRUE or FALSE values.
The multiplication (*) between the above two conditions acts like an AND operation.
It ensures that only rows where both conditions are TRUE (i.e., ‘Tablets’ in column B and a value greater than 180 in column C) are included. In Excel, TRUE equals 1, and FALSE equals 0. So, TRUE * TRUE = 1 (included), and any other combination results in 0 (excluded).
- The MEDIAN function calculates the median of the filtered range returned by the FILTER function.
Also read: How to Find Outliers in Excel?
Method #3: Using IF Function With PERCENTILE.INC
You can use a formula combining the PERCENTILE.INC and IF functions to calculate the median (50th percentile) of an array of values in Excel.
The PERCENTILE.INC function calculates the k-th percentile of a dataset where k is a value between 0 and 1, inclusive. The function is only available in Excel 365 and later.
Below is the syntax of the function:
PERCENTILE.INC (array, k)
The ‘array’ argument is required and is the array of values from which you want to find the k-th percentile.
The ‘k’ argument is required and is the value between 0 and 1, inclusive. For instance,0.25 represents the 25th percentile, 0.5 is the 50th percentile, and 1 is the 100th percentile, the maximum value.
Suppose you have the price list below for desktops, laptops, and tablets and you want to calculate only the median price of desktop computers.
You can compute the desired median price using the formula below:
=PERCENTILE.INC(IF(B2:B16="Desktops",C2:C16),0.5)
The above formula filters the values in the cell range C2:C16 where the corresponding value in B2:B16 is ‘Desktops.’ The PERCENTILE.INC function computes the 50th percentile or median of the filtered values.
Check For Multiple Conditions
You can check for multiple conditions inside the IF function. For instance, you can use the formula below to find the median price of only those tablets costing more than $180:
=PERCENTILE.INC(IF((B2:B16="Tablets")*(C2:C16>180), C2:C16), 0.5)
The formula uses the IF function to filter the values in the cell range C2:C16 where the corresponding entry in B2:B16 is ‘Tablets’ and the value in C2:C16 is greater than 180. PERCENTILE.INC function calculates the 50th percentile or median of the filtered values.
I have shown you three ways to calculate MEDIAN IF or a conditional median in Excel. I hope you found the tutorial helpful.
Other articles you may also like:
