To write the MEDIAN IF formula in Excel you need to use the MEDIAN function combined with the IF Function. First, you need to use the MEDIAN function, and then to create a condition you need to enter the IF function. In the IF, refer to the range on which you want to test the condition. After that, refer to the range where you have the data. In the end, enter the formula as an array formula.
In this tutorial, we will learn to write a formula to calculate a median with a condition.
Conditional Formula to Calculate MEDIAN IF
You can use the below steps:
- First, enter the MEDIAN function in a cell.
- After that, enter the IF function with the MEDIAN.
- Now, in the (logical_test) argument refer to the range A1:A1.
- From here in the same argument enter an equal to the operator and enter “Day 1” to use as a condition to test.
- Next, in the (value_if_true) in IF, refer to the range B1:B12 where you have data to calculate MEDIAN.
- In the end, enter closing parentheses to close the function. And use Ctrl + Shift + Enter to get the result from the formula.
=MEDIAN(IF(A1:A12="Day 1",B1:B12))How this Formula Works
To understand this formula, you need to understand that when entering a formula as an array, it works on the entire range instead of a single cell. You need to break this formula into two parts to understand this.
In the FIRST PART, you have a condition to test from the range A1:A12. In this range we have two values, Day 1 and Day 2, we want to test for the cells where we have the cell Day 1. As you can see in the above example, we have TRUE for all cells where we have the value Day 1 and FALSE for the rest of the cells.
After that, in the second argument of the IF function, you have referred to the range B1:B12 where we have the data to calculate the median.
In the SECOND PART, IF returns data for all the values for which we have DAY 1 in the range A1:A12, and FALSE for the rest of the values. And MEDIAN returns the median only for those values.