Excel track · Exercise 11 of 86 Paid
LARGE and SMALL: the nth highest
Pick the nth value in an unsorted list instead of sorting it to read one number.
Intermediate
Swim times and five supplier quotes
Read-only data · screen 1 of 2 · answer in C2
Put the second-best time in the team into C2, keeping the list unsorted.
| A | B | C | |
|---|---|---|---|
| 1 | Swimmer | Seconds | Second best |
| 2 | Ada | 58 | |
| 3 | Ben | 61 | |
| 4 | Cleo | 55 | |
| 5 | Dev | 57 | |
| 6 | Eli | 50 |
Put the second-cheapest quote into C2 without re-sorting the table.
| A | B | C | |
|---|---|---|---|
| 1 | Quote | Cost | Second cheapest |
| 2 | Q-1 | 40 | |
| 3 | Q-2 | 35 | |
| 4 | Q-3 | 52 | |
| 5 | Q-4 | 38 | |
| 6 | Q-5 | 47 |
Reference answer
What it does
LARGE returns the value at a given position counted from the top of the list, and SMALL counts the same way from the bottom. The second argument is a rank, so 1 is the highest and 2 the one after it. Nothing is sorted: the sheet keeps its rows and the answer sits wherever the formula is written.
Here the times run 58, 61, 55, 57 and 50, so the highest is 61 and the second highest is 58. The quote column works the other way: 35 is the cheapest and 38 the next one up, which is what SMALL with a 2 returns.
The position is the usual mistake, because 2 looks like a row number. A position of 0, or one past the end of the list, returns #NUM! where a value should be. A headline built on LARGE needs a guard when the list can shrink.
Syntax
=LARGE(range, n) and =SMALL(range, n)Worked example
On Swim times and five supplier quotes, =LARGE(B2:B6,2) returns 58.
=LARGE(B2:B6,2)Common errors
| Error | What it means and how to fix it |
|---|---|
#NUM! | The position is 0 or larger than the count of values, and LARGE has no such place in the order. |
#VALUE! | The first argument holds text rather than a block of numbers, and text has no order to count through. |
Wrong end | LARGE on a list of times returns the slowest and SMALL the fastest, so the number is right and the label above it is wrong. |
At work
A team sheet naming the second-best time, and a tender list tracking the second-cheapest quote, both without re-sorting the table.