How to / Sums and averages
How to calculate a median with a condition in Excel
Excel has no MEDIANIF. In Excel 2021 and Microsoft 365, use =MEDIAN(FILTER(B2:B6, A2:A6="Red")).
The median score of one team
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Team | Score | ||
| 2 | Red | 10 | 30 | |
| 3 | Blue | 40 | ||
| 4 | Red | 30 | ||
| 5 | Red | 50 | ||
| 6 | Blue | 20 |
In D2: =MEDIAN(FILTER(B2:B6, A2:A6="Red")) → 30
The Red scores are 10, 30 and 50.
How it works
- FILTER keeps the scores on the rows where the team is Red.
- MEDIAN finds the middle value of what is left.
- Unlike an average, the median is not pulled up or down by one unusual score.
Variations
In older versions of Excel
A B C D 1 Team Score 2 Red 10 30 3 Blue 40 4 Red 30 5 Red 50 6 Blue 20 In D2:
=MEDIAN(IF(A2:A6="Red", B2:B6))→ 30In Excel 2019 and earlier, finish it with Ctrl+Shift+Enter.
Two conditions
A B C D 1 Team Score 2 Red 10 40 3 Blue 40 4 Red 30 5 Red 50 6 Blue 20 In D2:
=MEDIAN(FILTER(B2:B6, (A2:A6="Red")*(B2:B6>=20)))→ 40Multiply the tests: both must be true.
The average with a condition
A B C D 1 Team Score 2 Red 10 30 3 Blue 40 4 Red 30 5 Red 50 6 Blue 20 In D2:
=AVERAGEIF(A2:A6, "Red", B2:B6)→ 30The highest with a condition
A B C D 1 Team Score 2 Red 10 50 3 Blue 40 4 Red 30 5 Red 50 6 Blue 20 In D2:
=MAXIFS(B2:B6, A2:A6, "Red")→ 50
Watch out for
- No rows match. FILTER returns #CALC! when nothing matches, and so does the whole formula. Give FILTER a third argument, or wrap the formula in IFERROR.
- Ranges of different sizes. The scores and the teams must cover the same rows.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- MEDIAN: The middle number of a sorted list.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
- AVERAGEIF: Averages the cells that meet a condition.
- MAXIFS: The largest value among cells that meet conditions.