Statistical
Conditional MEDIAN and MODE (MEDIANIFS / MODEIFS with FILTER)
Nest FILTER inside MEDIAN and MODE.SNGL to calculate outlier-resistant group medians and most-common values without helper columns.
Why compensation and latency benchmarks need conditional MEDIAN
Spreadsheets provide AVERAGEIFS, MAXIFS, and MINIFS, but no built-in MEDIANIFS or MODEIFS. In a 4-person Sales team earning $68k, $68k, $72k, and $192k (VP), AVERAGEIFS reports $100,000—more than 3 of the 4 reps make!
Passing FILTER(B2:B6, A2:A6=C2) directly into MEDIAN returns $70,000 (the midpoint of $68k and $72k), while MODE.SNGL returns the most repeated base band ($68,000).
Assume numeric, populated salaries. Empty values and missing groups need separate validation. MODE.SNGL returns #N/A when no value repeats. The mean and median describe different aspects of a distribution; the mean is not inherently an incorrect statistic.
| Dept (A) | Salary (B) | Sales Metric | Value |
|---|---|---|---|
| Eng | 95000 | AVERAGEIFS (skewed) | 100000 |
| Sales | 68000 | MEDIAN(FILTER(...)) | 70000 |
| Sales | 72000 | MODE.SNGL(FILTER(...)) | 68000 |
| Sales | 68000 | ||
| Sales | 192000 |
Conditional MEDIAN and MODE.SNGL formulas
Multiply criteria arrays inside FILTER—such as (A2:A6="Sales") * (B2:B6<150000)—whenever you need multi-criteria MEDIANIFS.
=MEDIAN(FILTER(B2:B6, A2:A6=C2))Compare conditional MODE.SNGL and skewed AVERAGEIFS
MODE.SNGL(FILTER(B2:B6, A2:A6=C2)) identifies the $68,000 modal salary.
=MODE.SNGL(FILTER(B2:B6, A2:A6=C2))Try the example
Use the original values or change them to test your understanding.