Return the k-th largest value in a range
LARGE(array, k)
LARGE will select a value based on its relative positioning in a range of values, for example first, second or third place from the largest value. It will evaluate cell references (A1,A2,A3) or ranges (A1:A3).
All numbers including negative values, percentages, dates, fractions, and times are assessed. Non-numeric values (e.g. text, TRUE/FALSE and empty cells) are ignored by the LARGE function.
If the arguments contain no numbers, LARGE returns the #NUM! error. Error values in the array argument cause errors.
LARGE examples.
In the examples below, the LARGE function is used to identify the 3rd and 5th largest values from arrays of population and GDP per capita data and (using an array formula with the LEN function) the 7th largest number of characters in a selection of text strings.
Use the LARGE function to return the k-th largest value in a range of values.
Syntax
=LARGE(array, k)
The LARGE function consists of 2 arguments:
- array. Required. The array or range of values from which to return the k-th largest value in a range.
- k. Required. The position (e.g. 1st [k=1], 5th [k=5], 11th [k=11]) to return.
Try it now!
- Enter your formulas into the grey cells.
Scope
LARGE can be applied to cell references, ranges, named ranges, tables and arrays.
|
=LARGE((C3,C5,C7),2) |
|
=LARGE(C3:C7,2) |
|
=LARGE(myNamedRange,5) |
|
=LARGE(Table1[ColumnName],9) |
Caveats
The LARGE function:
- returns the #NUM! error if the array is empty.
- returns the #NUM! error if k is less than or equal to zero (<=0).
- returns the #NUM! error if k is greater than the number of data points.
- will assess all numbers including negative values, percentages, dates, fractions, and times.
- will ignore text values, empty cells and TRUE and FALSE values.
- will trigger an error if the arguments contain errors.
Related Functions
- MAX: return the largest value in a range of values.
- MIN: return the smallest value in a range of values.
- SMALL: returns the k-th smallest value in a data set
- RANK: return the rank of a number in a data set
Functions Category