Function guides · Statistical
LARGE function
Returns the k-th largest value in a range.
Syntax
=LARGE(array, k)
| Argument | What it is |
|---|---|
array | The range of numbers. |
k | The rank to return: 1 is the largest, 2 the second largest, and so on. |
What LARGE does
LARGE returns the value at a given rank from the top. LARGE(range,1) is the biggest, LARGE(range,2) is the next biggest, and so on.
It fills the gap between MAX, which gives only the top value, and a full sort. For “the runner-up”, “the top three” or “the fifth-highest”, it is the direct tool, with no need to reorder the data. The partner function is SMALL, which counts from the bottom.
LARGE ignores text and blanks inside the range, and returns #NUM! if you ask for a rank beyond the count of numbers.
When the same list needs a leaderboard, put the ranks 1 to 5 in a column and fill LARGE down beside them, pointing at the rank cell. If someone adds a sixth rep, widening the range updates every row. Ties are the one complication, and a small helper column that adds a tiny amount based on the row number is the usual cure.
LARGE examples
1. Second highest sale
| A | B | |
|---|---|---|
| 1 | Day | Sales |
| 2 | Mon | 860 |
| 3 | Tue | 1240 |
| 4 | Wed | 910 |
| 5 | Thu | 1105 |
| 6 | Fri | 990 |
| 7 | 1105 |
LARGE with k = 2 returns the second largest value. The top sale is 1,240, and 1,105 is the next one down.
2. Total of the top three
| A | B | |
|---|---|---|
| 1 | Day | Sales |
| 2 | Mon | 860 |
| 3 | Tue | 1240 |
| 4 | Wed | 910 |
| 5 | Thu | 1105 |
| 6 | Fri | 990 |
| 7 | 3335 |
An array constant lets one formula pick several ranks. {1,2,3} asks for the first, second and third largest, and SUM adds them: 1,240 + 1,105 + 990.
3. Name of the runner-up
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Units | ||
| 2 | Dana | 410 | Dana | |
| 3 | Luis | 295 | ||
| 4 | Mei | 520 | ||
| 5 | Omar | 180 |
LARGE returns a value, so a lookup finishes the job. MATCH finds the position of the second-largest figure and INDEX reads the name beside it.
This breaks if two people tie, because MATCH returns the first one in both cases.
Common LARGE mistakes
- Asking for k larger than the count. LARGE(range,10) on five numbers returns #NUM!. Keep k within the number of values.
- Treating ties as separate ranks. With two equal top values, LARGE(range,1) and LARGE(range,2) return the same number. Rank-based lookups then return the same name twice.
- Using it on text. Text is ignored. If the range holds numbers stored as text, k can run out of values.
- Hard-coding k. Put the rank in a cell so the same formula can be filled down a Top N list.
LARGE on Excel job tests
Look for "the second highest", "the top three" or "the bottom five" and reach for LARGE or SMALL. For a top-N table, put the ranks 1, 2, 3 in a column and write =LARGE(range, A2) beside them.
For names, wrap the result in INDEX and MATCH, but know that it breaks on ties, because both tied rows return the first name. If the data has ties and the task wants every name, a helper column that adds a tiny amount based on the row number separates them.
To add the best three, use SUM with an array constant: SUM(LARGE(range,{1,2,3})).
Related functions
LARGE questions
- What is the difference between LARGE and MAX?
- MAX returns the largest value. LARGE returns the k-th largest, so LARGE(range,1) equals MAX.
- How do I sum the top three values?
=SUM(LARGE(range,{1,2,3}))adds the three largest.- What happens with duplicate values?
- Each copy takes its own rank, so two equal highs return the same number for k = 1 and k = 2.
- What does LARGE return for k = 1?
- The same as MAX.
Practice LARGE
The lesson PERCENTILE, LARGE and SMALL (module 20 of Analyst) has checked LARGE exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample