LARGE function
LARGE returns the nth biggest number in your data, such as the second highest sale or a top-three score. A position of 1 gives the same answer as MAX.
Example
Read it as a sentence: find the second largest number in A1:A8, which gives 7.
Excel shows 7
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
LARGE(array, k)
-
array(required) - Range or array of values.
-
k(required) - Rank from the top: 1 is the largest.
Returns: The k-th largest value in the data.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=LARGE({3,9,5,7},2) | 7 | Array constant |
=LARGE(5,1) | 5 | Single number as array |
=LARGE(Data!A1:A8,2) | 7 | Range k=2 |
=LARGE(Data!A1:A8,1.5) | 7 | Fractional k |
=LARGE(Data!A1:A8,{1,2,3}) | 9 | Array k (spills) |
=LARGE(Data!B1:B8,1) | 40 | Mixed range (text, bools ignored) |
=LARGE(Data!A1:A8,0) | #NUM! | K=0 |
=LARGE(Data!A1:A8,9) | #NUM! | K > count |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
1
2
10
2
4
20
3
4
TRUE
4
4
5
5
abc
6
5
30
7
7
FALSE
8
9
40
Data
Watch out for
- k of 1 equals MAX.
- k larger than the count returns #NUM!.
- Duplicates are each counted separately.
Where it works
- Excel
- LARGE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .