Report a call center's hold times without one outlier setting the headline number
LARGE and SMALL pick the nth-ranked value straight out of a range, and paired with SUM, MAX, MIN and COUNT they build a trimmed average without deleting a single row.
Task:
You supervise the afternoon shift at a customer support call center. Seven calls came through today, each logged with its hold time in minutes. For the shift report, work out the second-longest hold time in B9 and the second-shortest hold time in B10 — so a single freak call never gets quoted as "the" longest or shortest wait. Then in B11 work out the average hold time with that one longest and one shortest call set aside, so an unusually bad or unusually lucky connection doesn't skew the number you report up the chain.
Learning Objectives:
- Pull the nth-largest and nth-smallest value out of a range with LARGE and SMALL
- Build a trimmed average that sets aside the extremes without deleting them from the data
- Combine SUM, MAX, MIN and COUNT into a single average formula
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 | |
|---|---|---|
| 1 | Call ID | Hold Time (min) |
| 2 | CALL-101 | 5 |
| 3 | CALL-102 | 12 |
| 4 | CALL-103 | 20 |
| 5 | CALL-104 | 3 |
| 6 | CALL-105 | 8 |
| 7 | CALL-106 | 15 |
| 8 | CALL-107 | 9 |
| 9 | 2nd-Longest Hold Time (min) | |
| 10 | 2nd-Shortest Hold Time (min) | |
| 11 | Trimmed Average Hold Time (min) |
What this exercise teaches (contains the answer)
A plain AVERAGE would let one freak call — a customer who forgot they were on hold, a line that dropped and reconnected — pull the reported figure away from what a typical caller actually experienced. Subtracting MAX and MIN from the SUM removes exactly those two extremes from the total without deleting either row from the log, and COUNT minus 2 is the right divisor precisely because two values were set aside, not just one. LARGE and SMALL then name what those two outliers actually were, in B9 and B10, so the report says which calls got excluded instead of handing up a suspiciously tidy number with no accounting for it.