Excel has an in-built MEDIAN function, but there is no MEDIANIF function.
But that’s not an issue.
In this article, I will show you how you can create your own MEDIAN IF function by combining the MEDIAN and the IF function. I will also show you how to do the same using PERCENT.INC function.
MEDIAN IF Function
MEDIAN function in Excel allows us to find the median value in a dataset.
With the Median IF function, the aim is to find the median value of only those values that satisfy a given criteria.
For example, you might want to find the median salary of employees in a particular department or the median price of products in a specific category. The “IF” part allows you to set these conditions, while the “median” calculation provides the middle value of the resulting subset.
Let’s see how to do this in Excel.
Using MEDIAN and IF Function
The most straightforward approach to calculating a conditional median in Excel is to combine the MEDIAN and IF functions.
Below, I have a data set where I have the employee name in column A, their department in column B, and the salaries in column C.
Now I want to find out the median salary for only those employees who work in the sales department.
I can do this using the below formula:
=MEDIAN(IF(B2:B11="Sales",C2:C11))
The above formula uses the IF function to check the department and only gives us the salary value for those people who work in the Sales department.
Important Note: If you’re using an older version of Excel that does not have dynamic arrays, you need to enter it by pressing Ctrl+Shift+Enter instead of just Enter. This tells Excel to treat the formula as an array formula. In newer versions of Excel (365 and later), this step is often unnecessary, as Excel can recognize and handle array formulas automatically.
If you want to check for multiple conditions, you do that within the IF function itself.
For example, if I want to know the median salary in the Sales department, but only for those people whose salary is more than 200K, I can use the below formula:
=MEDIAN(IF((B2:B11="Sales")*(C2:C11>200000),C2:C11))
Note: In case there are errors in your data set, you’ll have to use the IFERROR function to handle it
Also read: Calculating Weighted Average in Excel
Using PERCENT.INC and IF Function
Another workaround to create a MEDIAN IF function in Excel is by using the PERCENTILE.INC function.
Below, I have a data set with the employee name in column A, their department in column B, and their salaries in column C.
Now I want to find out the median salary for only those employees who work in the sales department.
Below is the formula that would give me the median value while satisfying the condition:
=PERCENTILE.INC(IF(B2:B11="Sales",C2:C11),0.5)
In the above PERCENTILE.INC formula, I have used 0.5 as the second argument, which gives me the 50% percentile value (which is also the median value).
The advantage of this method is that you can use the same formula with a minor tweak to get the 30 percentile value or 75 percentile value. Just change the value from 0.5 to whatever percentile value you want.
In this article, I showed you how to create a MEDIANIF function using a combination of existing functions in Excel. You can do this by using a combination of MEDIAN and IF functions, and if you need more control, then you can try using PERCENT.INC with IF function.
I hope you found this article helpful.
If you have any questions or suggestions for me, please let me know in the comments section.
Other Excel articles you may also like:
- Calculate Interquartile Range (IQR) in Excel
- Calculate Average Annual Growth Rate (AAGR) in Excel
- Calculate Compound Annual Growth Rate (CAGR) in Excel
- Calculating Moving Average in Excel [Simple, Weighted, & Exponential]
- Count Unique Values in Excel Using COUNTIF Function
- Calculate Running Total in Excel (Cumulative Sum)
- Calculate P-value in Excel
