Excel track · Exercise 12 of 86 Paid
MEDIAN: the middle value
Summarise a list with a middle value that an outlier cannot drag.
Intermediate
Five and six audit readings with one outlier
Read-only data · screen 1 of 2 · answer in C2
Put the typical reading into C2, choosing the summary that one noisy reading cannot drag.
| A | B | C | |
|---|---|---|---|
| 1 | Reading | Value | Typical |
| 2 | R-1 | 12 | |
| 3 | R-2 | 18 | |
| 4 | R-3 | 14 | |
| 5 | R-4 | 19 | |
| 6 | R-5 | 15 |
The audit has an even number of readings, so put the median into C2 and accept the half-way value.
| A | B | C | |
|---|---|---|---|
| 1 | Reading | Value | Median |
| 2 | R-1 | 12 | |
| 3 | R-2 | 18 | |
| 4 | R-3 | 14 | |
| 5 | R-4 | 19 | |
| 6 | R-5 | 15 | |
| 7 | R-6 | 40 |
Reference answer
What it does
MEDIAN returns the middle value once the numbers are put in order. Half the readings sit above it and half below, so one extreme figure barely moves it. With an odd count the answer is one of the values on the sheet.
The first screen has five readings, 12, 18, 14, 19 and 15, which sort to 12, 14, 15, 18 and 19, so the answer is 15. The second screen adds a reading of 40, and the mean over the six climbs to 19.67. MEDIAN returns 16.5 instead, the half-way point between 15 and 18.
Blanks and text are ignored rather than counted as zero, so MEDIAN copes with a column that is still being filled. Point at the range and not at the six figures, or the next reading gets left out.
Syntax
=MEDIAN(range)Worked example
On Five and six audit readings with one outlier, =MEDIAN(B2:B6) returns 15.
=MEDIAN(B2:B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#NUM! | The range holds no numbers at all, which leaves MEDIAN no middle value to report. |
#VALUE! | A word was passed as an argument, as in =MEDIAN("n/a"), so it cannot join the sort. |
Wrong middle | A range that stops one row short changes the answer: five readings give 15 where six give 16.5. |
At work
An audit report where one late-night reading should not move the typical figure, and salary bands quoted as medians.