AVERAGE with an Outlier
The mean is a fair summary only when nothing in the data is extreme.
Task:
You are quoting for next month and want to know what a typical job is worth. Put the average of the five invoices in B7, then look at how it compares to the invoices themselves.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of AVERAGE
0/3The same formula in the other shapes it takes at work.
- AVERAGE with an Outlier
The mean is a fair summary only when nothing in the data is extreme.
CoreBeginner - A trimmed average: drop the highest and lowest
Take out the two extremes before averaging, the way judging panels do.
Intermediate - AVERAGEIFS: an average under two conditions
The typical value for one group, ignoring the small cases.
Advanced
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 | |
|---|---|---|
| 1 | Job | Invoiced |
| 2 | Logo refresh | 450 |
| 3 | Brochure | 520 |
| 4 | Website copy | 480 |
| 5 | Packaging | 610 |
| 6 | Rebrand programme | 8400 |
| 7 | Average |
What this exercise teachesMay contain the answer
The average job is worth 2092, and four of the five jobs came in under 620. A single rebrand programme has dragged the mean past almost every real invoice, so quoting it would be indefensible. This is the failure the rest of this track exists to fix: MEDIAN, a few exercises from here, is what you reach for when one value is doing this.