Why does LARGE return #NUM! for a top 5 list?
Formula (Excel and Google Sheets)
=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))You asked for more places than there are numbers. Leave the extra places blank: =IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2)).
- Result on the example
- one result per row in
E2, filled down toE6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2, filled down to E6. Change any input and the result updates, or edit the formula to experiment.
How it works
LARGE(range, k) returns the k-th largest number, and k can't be larger than the count of numbers. With four scores there is no 5th place yet, so LARGE returns #NUM!. COUNT says how many scores there are, and IF leaves the extra places blank until more scores arrive.
=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))Result9115
D21The value in D2.
$B$2:$B$65 rows × 1 col: 8420, 9115, 7760, 8890, …A range: 5 cells in one column.
COUNT($B$2:$B$6)4COUNT: Counts cells that contain numbers.- value1
$B$2:$B$65 rows × 1 col: 8420, 9115, 7760, 8890, …
- value1
D2>COUNT($B$2:$B$6)FALSEThe > operator tests greater than.
LARGE($B$2:$B$6,D2)9115LARGE: k-th largest value.- array
$B$2:$B$65 rows × 1 col: 8420, 9115, 7760, 8890, … - k
D21
- array
IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))9115IF: Returns one value if a test is true, another if false.- logical_test
D2>COUNT($B$2:$B$6)FALSE - value_if_true
""not used - value_if_false
LARGE($B$2:$B$6,D2)9115
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFERROR(LARGE($B$2:$B$6,D2),"") | 9115, 8890, 8420… | Shorter, but it would hide any other error too. |
Common mistakes
- A k of 0 or less also returns #NUM!.
- Scores stored as text aren't counted, which makes k "too large" sooner than you expect.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does LARGE return #NUM! for a top 5 list
E2, return the score at the rank in D2, or a blank when there aren't that many scores yet. Fill down to E6.Questions
Does SMALL work the same way?
Yes, and it returns #NUM! for the same reason.
How do I list the top 5 in Excel 365?
=TAKE(SORT(B2:B6,,-1),5) returns as many as exist, without errors.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.