Excel like a finance pro.
MEDIAN & MODE
AggregationAverage deal size gets dragged around by one whale. The median describes the business.
Difficulty
1What is it?
An average is honest only when the values are roughly evenly spread, and business data rarely is: one enormous customer, one huge deal, one unusual month. The mean moves toward the outlier and stops describing anyone. MEDIAN returns the middle value, which a single whale can't pull. MODE returns the most common one — the typical order size, the usual payment term. When mean and median disagree sharply, that disagreement is itself the finding: the data is lopsided, and a plan built on the average will be wrong for most of it.
2What it looks like
MEDIAN(number1, [number2], …) · MODE.SNGL(number1, …) · MODE.MULT(number1, …)- number1, …
- The values or range. Text and blanks in a range are ignored; zeros are not.
3When you use it
- Report typical deal size without one enterprise contract distorting it.
- Find the most common invoice amount or payment term.
- Test whether a distribution is lopsided by comparing mean to median.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Grow the biggest deal and watch the mean chase it while the median stays put.
=MEDIAN(B2:B6)AVERAGE: $28,240 — describes none of these deals| A | B | C | |
|---|---|---|---|
| 1 | Deal | Size | Result |
| 2 | 1 | 4,000 | AVERAGE: $28,240 — describes none of these deals |
| 3 | 2 | 5,500 | MEDIAN: $5,500 — the typical deal |
| 4 | 3 | 5,500 | MODE.SNGL: $5,500 — the most common size |
| 5 | 4 | 6,200 | When mean and median disagree this much, the disagreement IS the finding. |
| 6 | 5 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#N/A from MODENo value repeats, so there is no most-common value.Fix: Real answer, not a bug. MODE only means something on data with genuine repetition.
Median is dragged toward zeroBlank cells were filled with 0, and zeros count.Fix: Leave genuinely missing values blank — MEDIAN skips blanks but not zeros.
MODE.SNGL hides a tieTwo values are equally common and it returns only the first.Fix: MODE.MULT returns all of them, as a spilled array.
Mean and median wildly differentThe data is skewed. That IS the result.Fix: Say so in the report, and plan on the median for the typical case.
6Better functions & alternatives
- AVERAGE — Right when values are evenly spread. Dangerous on anything with a long tail, which is most revenue data.
- TRIMMEAN — An average with the top and bottom few percent dropped — a middle ground between mean and median.
Want MEDIAN & MODE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.