Excel functions / Maths & statistics
LARGE & SMALL
Return the nth largest or smallest number, such as the 2nd highest score.
Syntax
=LARGE(array, k)
=SMALL(array, k)Arguments
array | The numbers to rank. |
|---|---|
k | Which position: 1 is the largest (or smallest), 2 the next, and so on. |
How it works
MAX gives you the top value; LARGE gives you any place on the podium. =LARGE(range,1) is the same as MAX, =LARGE(range,2) is the runner-up.
SMALL counts from the bottom instead. Together they are handy for top-3 and bottom-3 lists.
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 500 | 500 | |
| 3 | West | Ben | 300 | ||
| 4 | East | Cal | 250 | ||
| 5 | West | Dee | 700 | ||
| 6 |
Result: 500. The second-highest sale.
Tips
- Duplicates count separately: in 9, 9, 5 the 2nd largest is 9.
Practise LARGE & SMALL
Try it yourself in a real spreadsheet, with instant feedback on every answer.