LARGE and SMALL
About this lesson
LARGE returns the k-th largest number in a range. SMALL returns the k-th smallest. So LARGE(range,2) is the second-highest value.
- find the second-highest or third-lowest value in a range.
- add up a top three, and say what LARGE does with ties.
The idea
MAX answers one question. LARGE answers the same question for any position: the second-highest, the third, the tenth. The second argument is the position. With 1 in it, the function is exactly MAX.
The problem is ties. LARGE ranks entries, not different values. A list with two 26s returns 26 for position 1 and for position 2. If "second highest" has to mean the next different number, LARGE cannot do it alone. The right tool is a sorted list with the duplicates removed, which the Dynamic Arrays module builds.
The mistake to watch for
LARGE ranks entries, not different values. With two reps on 26 deals, LARGE with 1 and LARGE with 2 both return 26. So the "second-highest" figure in a report is the same as the highest. When second highest has to mean the next different number, LARGE cannot do it alone.
Read more about this
Where this comes up again
This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.
In E2, type:
=LARGE(B2:B9,1)
That finds the best deal count.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Deals | |||||
| 2 | Ade | 14 | Best | ||||
| 3 | Bea | 9 | Second best | ||||
| 4 | Cal | 21 | Fewest | ||||
| 5 | Dev | 17 | Top three together | ||||
| 6 | Eli | 9 | |||||
| 7 | Fay | 26 | |||||
| 8 | Gus | 12 | |||||
| 9 | Hal | 17 |
Every step
-
In
E2, type:=LARGE(B2:B9,1). That finds the best deal count. -
In
E3, find the second-best deal count withLARGE. -
In
E4, find the fewest deals withSMALL. -
Two reps tied with 9 deals each. Read
=SMALL(B2:B9,2)and say what it returns. -
In
E5, add the three largest counts withLARGE. Shape:=LARGE(range,1)+LARGE(range,2)+LARGE(range,3).