In this tutorial, you’ll learn how to calculate a conditional median (or Median IF function) in Excel. This means finding the median value of a dataset where a certain condition is met. While Excel doesn’t provide a built-in way of calculating a median IF, we can find a simple and intuitive way of taking on this task. Remember, the median represents the middle value of a dataset. Because of this applying conditions to a dataset will change the middle value.
By the end of this tutorial, you’ll have learned the following:
- What a median if (or condition median) is
- How to calculate a median IF in Excel
Understanding Median IF (Conditional Median)
A Median IF is a function that will find the middle value of a dataset where a certain condition is met. For example, if you have a dataset that contains points scored for different divisions and teams. You may want to calculate the median number of points scored for each division, rather than for the entire dataset.
Developing a conditional IF function allows you to dynamically calculate the median for a particular condition. This is particularly important when your data is often changing. Normally, you would have to filter your data or specify exactly which points to include. By using a median IF function, you can be sure that you’re using the most up-to-date data available in your workbook.
Calculate a Median IF Function in Excel (Microsoft 365)
Is there a Median IF function in Excel?
Excel does not provide a built-in Median IF function, unlike the AVERAGEIF() or AVERAGEIFS() functions. However, using a combination of the MEDIAN() and IF() functions, mixed with array formulas, you can easily create your own conditional median in Excel.
A Note About Array Formulas in Microsoft 365
Beginning in September 2018, Microsoft simplified how array formulas can be written. This is done using dynamic array formulas. You no longer need to use the CTRL+SHIFT+ENTER keyboard command to create an array formula. Instead, you can simply hit the Enter key and Excel will adapt the formula to be an array formula.
The steps below will show how to calculate a Median IF in Excel if you’re running a version newer than September 2018 on Microsoft 365. If you’re working with older versions of Excel that don’t support dynamic array formulas, you can click here to follow along.
Let’s dive into an example, where we calculate the median IF for a set of sports scores:
How to Calculate a Median IF (Conditional Median) in Excel
Load your dataset with your values split across different columns
Load a dataset that contains your data. In this case, we have team points broken out by team and their respective division. We’ll want to calculate the median for each of the divisions.
Use the MEDIAN () function to calculate the median value
Start writing your MEDIAN() function. Rather than passing in a range of values directly, we’re going to pass in the IF() function, which will help filter the data to only include values that we want to use.
Pass the IF() function into the MEDIAN() function to filter to a condition
Inside the nested IF() function, we write our dynamic array function. In this case, that’s A2:A33="Atlantic". This returns a boolean array containing only the records where the Division is equal to “Atlantic”. We then enter the array that we want to use for the MEDIAN() function’s calculation, which in this case are the filtered values in the Points column.
Complete your function to calculate the Median IF
When you hit Enter, the function evaluates. In this case, it returns the median value of the records where the Division is equal to “Atlantic”.
That wasn’t so bad! In this case, we returned a Median IF of 91, meaning that the median for teams in the Atlantic division is 91 points. In the following section, you’ll learn how to calculate the Median IF using array functions.
Calculate Multiple Median IF Functions in Excel
There may be times when you want to calculate the median IF for all the unique values in a given dataset. Thankfully, this is also quite easy to do! Microsoft 365 provides a function, UNIQUE(), that allows you to easily calculate the unique values in a given column. Let’s load the same dataset we used above and create a column of the unique divisions.
This generates a list of the unique divisions in the dataset, as shown below:
From here, we can recreate our dynamic array function. This time, however, we’ll use the first value in the array as our condition. We’ll also use absolute references in the array function and the median function, as shown below. When we do this, we calculate the median for the first unique value in the array, which in this case is 91 for the Atlantic division.
From here, we can simply drag the fill handle all the way down to fill in the conditional median for the remaining values. Because we used absolute references for part of the function, this works without issue!
We can see how easy it was to calculate the Median IF function for multiple values. By combining dynamic array functions with absolute references, we were able to calculate the conditional median for multiple values quite easily.
Calculate Median IF Functions in Excel with Traditional Array Functions
If you’re not working with Microsoft 365 and don’t have dynamic array formulas available to you, don’t worry! In this section, you’ll learn how to use traditional array functions in Excel to calculate a Median IF. The process works largely the same, although we’ll be using traditional array functions, rather than dynamic ones.
We’ll load the same dataset we’ve been using so far. As shown below, we have three columns and we want to calculate the median for the Atlantic division.
In this case, we’ll type in the following formula:
=MEDIAN(IF(A2:A33="Atlantic", C2:C33)Rather than simply hitting enter, in this case, you’ll hit CTRL+SHIFT+Enter. This creates an array formula instead. Notice also that the formula itself is wrapped in curly braces now!
Once you are done with the function, Excel returns the calculated conditional median value!
Conclusion
In this tutorial, you learned how to calculate a conditional median in Excel. While Excel doesn’t provide a MEDIAN IF function, you can replicate this behavior using array functions. Thankfully Microsoft 365 made this much simpler with the use of dynamic array functions. You first learned how to calculate the median IF in Excel for a single value as well as for an entire column. From there, you learned how to use traditional array functions to calculate the median IF in Excel for a single value.
Additional Resources
To learn more about related topics, check out the tutorials below: