AdvancedLookup & Reference
Name the third-fastest supplier
Return a name from one column based on a ranked value in another.
INDEX · MATCH · SMALL
13 min40 points
Measure the spread, ignoring the extremes
The situation
Harbor Supply's best week was a one-off restocking surge and its worst was the week of a blizzard. Planning wants the spread between the second-best and second-worst weeks instead, on the grounds that both extremes tell you nothing about a normal quarter.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Week | Revenue | |||
| 2 | Week 1 | $24,800 | |||
| 3 | Week 2 | $31,200 | |||
| 4 | Week 3 | $19,750 | |||
| 5 | Week 4 | $28,400 | |||
| 6 | Week 5 | $35,100 | |||
| 7 | Week 6 | $22,600 | |||
| 8 | Week 7 | $29,900 | |||
| 9 | Week 8 | $26,300 | |||
| 10 | |||||
| 11 | Spread, extremes excluded | ||||
| 12 |
Functions in this exercise
Who this is for
Recommended next
Related exercises
- BeginnerStatistical Functions
Find the second-lowest quote with SMALL
Pick out the second-cheapest supplier quote rather than simply the cheapest.
SMALL
4 min10 points - AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points - AdvancedLookup & Reference
Name the third-fastest supplier
Return a name from one column based on a ranked value in another.
INDEX · MATCH · SMALL
13 min40 points