Math & Statisticsintermediate
Find the maximum value by criteria with MAXIFS
=MAXIFS(D2:D100,B2:B100,"East")
Excel how-to guide
How to large rank top n in Excel
Find the top 3 sales figures, the second-highest temperature reading, or the fifth-largest order amount from a data column.
Step-by-step instructions
How to large rank top n — 3 steps
1
Reference the range of values you want to rank.
2
Enter the rank position as the second argument — 1 for the largest, 2 for the second largest, and so on.
3
Use LARGE in a sequence of cells with rank positions 1, 2, 3 to build a top-N leaderboard.
Example data
Worked example
| Sales Rep | Revenue |
|---|---|
| Alice | 45000 |
| Bob | 38000 |
| Carol | 52000 |
| David | 41000 |
| Eve | 49000 |
Common mistakes
Errors to watch out for
LARGE returns
The rank argument is larger than the number of values in the range. If you ask for LARGE(B2:B10,15) but the range has only 9 values, Excel returns #NUM.
Tied values return the same result
LARGE(range,1) and LARGE(range,2) both return the same number when the top two values are tied. This is correct behaviour — use RANK to assign positions that account for ties differently.
FAQ
Frequently asked questions
Is there a SMALL function for the Nth smallest value?
Yes — SMALL(range,k) returns the kth smallest value. SMALL(B2:B100,1) returns the minimum, equivalent to MIN.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Array Formulasbeginner
Sort a range dynamically with SORT
=SORT(A2:B20,2,-1)