Excel LARGE Function

The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
- array - An array or range of numeric values.
- k - Position as an integer, where 1 corresponds to the largest value.
The LARGE function returns a numeric value based on its position in a list when sorted by value. In other words, LARGE returns the "nth largest" value in the list where 1 corresponds to the largest value, 2 corresponds to the second largest value, etc. For example, the LARGE function is useful when you want to retrieve first, second, or third highest scores for a test.
The LARGE function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the largest value in array, provide 1 for k. To return the fifth largest value in array, provide 5 for k.
To get nth smallest values, see the SMALL function.
Examples
In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant:
=LARGE({29,14,33,19,17},3) // returns 19
Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd largest values in a range:
In the example shown, the formulas in G5, G6, and G7 are, respectively:
See below for more advanced formulas based on the LARGE function.
Notes
- LARGE ignores empty cells, text values, and TRUE and FALSE values.
- If array contains no numeric values, LARGE returns a #NUM! error.
- To determine the rank of a number in a data set, use the RANK function.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.