ExcelInsider Team
Finding the median of values that satisfy specific criteria is frequently necessary when analyzing commercial or real estate data. Although Excel lacks a built in MEDIAN IF function, you can calculate it by combining functions and formulas. In this tutorial, you’ll learn about MEDIAN IF in Excel and how to use MEDIAN IF to find conditional median in Excel for single and multiple criteria.
➤ Single criteria: In this article, we’ll learn to use the MEDIAN IF to obtain conditional median for single and multiple criteria using Excel’s MEDIAN, IF, and FILTER functions.=MEDIAN(IF(criteria,cell_reference))
➤ Multiple criteria: =MEDIAN(IF((criteria1)*(criteria2),cell_reference)) OR =MEDIAN(IF(criteria1,IF(criteria2,cell_reference)))
➤ MEDIAN and FILTER: =MEDIAN(FILTER(array,(criteria1)*(criteria2)))
➤ For Excel 2019 or earlier versions use Ctrl + Shift + Enter to apply the formula. On Excel 365 or later versions, just press Enter .
What is MEDIAN-IF Formula in Excel?
Median is the middle value when the observations in the dataset are arranged in ascending order. For an odd number of observations, the median is the midway value. For an even number of observations, the median is the average of the two middle values.
MEDIAN IF in Excel means conditional median, that is, calculating the median of values that meet single or multiple criteria. For instance, in a list of apartment and house prices for New York, Los Angeles, and San Francisco, we can determine the median price of houses or apartments in one of the three cities.
Using MEDIAN IF to Find Conditional Median in Excel: Single Criterion
Consider the city and house prices dataset containing the city, the house type, and the price from columns A through C.
Suppose you are a real estate agent who wants to calculate the median house prices across these three cities, or you may need to compare the median apartment prices for New York City and San Francisco. Let’s look at the steps to apply the MEDIAN IF to determine the conditional median for single or multiple criteria.
Steps:
➤ Select the output cell (B19) and enter the formula. For Excel 2019 or earlier versions, press the Ctrl + Shift + Enter keys. On Excel 365 or later versions, just press Enter .
=MEDIAN(IF(B2:B16=A19,C2:C16))
The IF function checks the B2:B16 range against the value in the A19 cell (House) and returns only the prices of the house, while the MEDIAN function calculates the median of these values.
Applying MEDIAN IF for Multiple Criteria
You can add multiple if conditions to the existing formula. This is because Excel’s IF function can handle arrays as input. But there is a quirk, you have to press a combination of keys to evaluate the array formula.
Steps:
➤ Select cell C19 and type in the formula below. If you are using an earlier version of Excel press Ctrl + Shift + Enter . On Excel 365 or later versions, just hit Enter .
=MEDIAN(IF((A2:A16=A19)*(B2:B16=B19),C2:C16))
The IF function checks both A2:A16 and B2:B16 ranges to match “New York” and “Apartment”. Then returns only the values that satisfy both conditions. Here, the asterisk symbol acts as the logical AND operator, allowing you to combine multiple criteria.
Conditional MEDIAN with FILTER Function
The FILTER function in Excel 365 or later versions makes it easy to use MEDIAN IF for conditional median. The MEDIAN function computes the median after the FILTER function filters the values that satisfy the criteria, thus avoiding array formula. This approach is dynamic, simple to understand, and adapts to changes in your dataset automatically.
Steps:
➤ Move to cell C19 and enter the formula below. For example, we calculated the MEDIAN IF for multiple conditions. The FILTER function returns the values that satisfy the two criteria (San Francisco City and House). The MEDIAN function calculates the median of these values.
=MEDIAN(FILTER(C2:C16,(A2:A16=A19)*(B2:B16=B19)))
➥ The median price of an apartment in New York is $1,075,000. In contrast, the median price of a house is $825,000. Therefore, apartment prices in New York are significantly higher compared to houses.
➥ In Los Angeles, the median prices for apartments and houses are $950,000 and $850,000. The narrow price difference suggests a stable market.
➥ The median prices for apartments and houses in San Francisco are $700,000 and $1,400,000. Unlike New York and Los Angeles markets, houses are more expensive than apartments in San Francisco.
Frequently Asked Questions
How to calculate the median with if condition in Excel?
=MEDIAN(IF(criteria,cell_reference))
Press Ctrl+Shift+Enter to apply this array formula.
How to calculate the median with if for multiple conditions in Excel?
=MEDIAN(IF((criteria1)*(criteria2),cell_reference))
OR
=MEDIAN(IF(criteria1,IF(criteria2,cell_reference)))
How do you calculate the average with if condition in Excel?
=AVERAGEIF(range,criteria,[average_range])
What is the difference between MEDIAN IF and AVERAGE IF?
MEDIAN IF calculates the middle value of a data set for a given condition. AVERAGE IF returns the arithmetic mean of the values that meet the condition.
How to calculate the conditional median based on the OR condition?
=MEDIAN(IF((criteria1)+(criteria2), range))
Which is better, MEDIAN IF or AVERAGE IF?
AVERAGEIF will be affected by extreme outliers. On the other hand, MEDIAN IF calculates the middle value for data arranged in ascending order. Thus, MEDIAN IF gives reliable results when the data has very large or very small values.
Wrapping Up
In this tutorial, we’ve learned to calculate the MEDIAN IF (conditional median) for single and multiple criteria using Excel’s MEDIAN, IF, and FILTER functions. Feel free to download the practice file and share your thoughts and suggestions.
