You can combine MEDIAN, and IF functions to calculate the conditional median in Google Sheets. And once you write the formula, you need to use Ctrl + Shift + Enter to enter it as an array formula.
In this tutorial, we will learn to write this formula.
Combine MEDIAN and IF
You can use the below steps:
- Enter the MEDIAN function in the cell.
- Insert the IF function after that.
- Refer to the range A1:A12.
- Type the = equal to and enter “Day 1”.
- For the second argument, refer to the range B1:B12, where we have the score.
- Insert closing parentheses.
Make sure to use Ctrl + Shift + Enter to enter the formula. And when you do that, it will wrap the entire formula with the Arrayformula, just like the below example.
=ArrayFormula(MEDIAN(IF(A1:A12="Day 1",B1:B12)))How Does This Formula Work?
To know how this formula works, you must understand that we have entered this formula as an array formula. That means it works on the entire range instead of a single cell. So, to understand it, you need to split it into three parts.
In the first part, with the help of IF, you have a condition to test with the entire range A1:A12. This condition checks which cell has the value “Day 1”.
For all the cells where you have “Day 1”, it returns TRUE, and for all rest, FALSE.
In the second argument of the IF, you have referred to the range B1:B12, where we have the data to calculate the median.
The final array returned by the IF is the values from column B, but only for the cells where the corresponding value is “Day 1”.
Ultimately, MEDIAN calculates the median for the available values and ignores the FALSE.
