A trimmed average: drop the highest and lowest
Take out the two extremes before averaging, the way judging panels do.
Task:
Six judges scored a performance and one of them was wildly out of line. In B9 give the average with the single highest and single lowest score removed.
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 | Judge | Score |
| 2 | J1 | 8.4 |
| 3 | J2 | 8.1 |
| 4 | J3 | 3 |
| 5 | J4 | 8.6 |
| 6 | J5 | 8.2 |
| 7 | J6 | 9.1 |
| 8 | ||
| 9 | Trimmed average |
What this exercise teachesMay contain the answer
One judge's 3.0 pulls the plain average down to 7.57, below five of the six scores. Dropping one score from each end leaves an average of the scores that agree, 8.33, which is why panels and many surveys do exactly this.