Report a fair "typical" salary when one outlier skews the average
MEDIAN finds the middle of a sorted list rather than folding every value into a total, so a single outlier can't drag it around the way it drags AVERAGE.
Task:
You handle compensation analysis in HR at a marketing agency, checking pay equity ahead of a scheduled review. Six base salaries are listed below: five marketing specialists and the department director. In C8, report the median base salary across all six people — the figure HR will actually quote, since the director's salary sits well above the specialists' and would drag a plain average upward. In C9, work out that plain average for comparison, so the gap between the two is visible. In C10, work out the standard deviation of just the five specialists' salaries, to show how tightly clustered a typical specialist's pay actually is.
Learning Objectives:
- Use MEDIAN to report a typical value that isn't pulled around by an outlier the way AVERAGE is
- Recognize when a plain average misrepresents a dataset that has one atypical value in it
- Choose a narrower range for STDEV than for MEDIAN or AVERAGE, when only part of the data describes the group being measured
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Role | Base Salary |
| 2 | Sofia Reyes | Specialist | 52000 |
| 3 | Ben Okafor | Specialist | 54000 |
| 4 | Grace Lindqvist | Specialist | 55000 |
| 5 | Marcus Yee | Specialist | 53500 |
| 6 | Talia Novak | Specialist | 56000 |
| 7 | Owen Fitzgerald | Director | 98000 |
| 8 | Median base salary (all 6) | ||
| 9 | Average base salary (all 6) | ||
| 10 | Std deviation — specialists only |
What this exercise teaches (contains the answer)
MEDIAN(C2:C7) sorts all six salaries and returns the average of the middle pair, 54000 and 55000, landing at 54500 — a figure four of the five specialists sit close to. AVERAGE(C2:C7) folds Owen's 98000 director salary into the same total the specialists share, pulling the mean up to 61416.67 even though nobody but Owen earns anywhere near that; the gap between the two numbers is exactly the distortion one outlier causes, which is why compensation reports usually quote a median rather than a mean. STDEV(C2:C6) deliberately stops at row 6, before Owen's row — run over all six salaries it would report a huge spread that says almost nothing about the specialists themselves, since nearly the whole figure would be explained by one director sitting apart from the group. Restricted to the five specialists it comes out to about 1516.58, showing that once the outlier is set aside, typical pay is actually clustered within about $1,500.