Description
Use this function to return the k-th largest value in a data set. Supported in Chains. Can be used with CHILDREFS.
LARGE is useful for extracting a value based on its relative standing in a dataset. It can be used to find top performers, outliers, or specific percentile values.
Syntax
LARGE(array, k)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The array or range of data from which to extract the k-th largest value. | An array of numbers or a reference to a range of cells containing numbers. |
k |
Yes | The position (from the largest) of the value to return. | A positive integer. If k is greater than the number of data points, LARGE returns the smallest value. |
Example
Sample data
| A | B |
|---|---|
| Score | Name |
| 85 | Alice |
| 92 | Bob |
| 78 | Charlie |
| 95 | David |
| 88 | Emma |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=LARGE(A2:A6, 1) |
Returns the highest score. | 95 |
=LARGE(A2:A6, 2) |
Returns the second highest score. | 92 |
=LARGE(A2:A6, 3) |
Returns the third highest score. | 88 |
=LARGE(A2:A6, 5) |
Returns the lowest score (5th largest). | 78 |
=INDEX(B2:B6, MATCH(LARGE(A2:A6, 1), A2:A6, 0)) |
Returns the name of the person with the highest score. | David |
Notes
- If the array is empty, LARGE returns the #NUM! error.
- If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error.
- Duplicate values are counted. For example, in a list of 10 scores with two scores of 100, both
LARGE(array, 1)andLARGE(array, 2)would return 100. - Non-numeric values in the array are ignored.
-
LARGE is often used in conjunction with its counterpart, the SMALL function:
- LARGE returns the k-th largest value
- SMALL returns the k-th smallest value
Tips
- Use LARGE to find top performers or identify outliers in a dataset.
- Combine LARGE with INDEX and MATCH to return associated information for the k-th largest value.
- To find percentiles, use LARGE with k calculated as
(1 - percentile) * count of numbers. - For dynamic ranges, consider using LARGE with array formulas or dynamic named ranges.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA