LARGE
Returns the second, third or any k-th largest number: the top 3 sales, the three biggest orders.
LARGE(array, k)- array
- A range of numbers, for example $B$2:$B$8.
- k
- Which place you need: 1 gives the largest number, 2 the next one after it.
Return value
The k-th largest number: with k = 1 the largest, with k = 2 the second largest. If k is greater than the count of numbers, you get a #NUM! error.
Examples
Top 3 sales
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
10
Rep
Sales, $
Place
Sales, $
Johnson
561800
1
Miller
469400
2
Davis
605200
3
Wilson
354400
Moore
498700
Taylor
412300
Thomas
530900
Sheet1
Details
LARGE with k = 1 works like MAX, with k = 2 it returns the second largest number, and so on. This is how you build a top N without sorting the table.
If two numbers are equal, each takes its own place: with two top amounts of 200,000, both k = 1 and k = 2 return 200,000.
Common mistakes
- A range without $ signs: when you drag the formula, it shifts down, and the top is taken from an incomplete list.
- k is greater than the count of numbers in the range, or less than 1: the formula returns #NUM!.
- LARGE returns only the amount, not the name. Look up the name separately, for example with INDEX and MATCH.