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 | ||
|---|---|---|---|
1 |
Score | Name | |
2 |
85 | Alice | |
3 |
92 | Bob | |
4 |
78 | Charlie | |
5 |
95 | David | |
6 |
88 | Emma |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the highest value in a data set. | =LARGE(A2:A6, 1) |
Returns the highest score among the five students. Result: 95 |
| Find the value at a specific rank from the top of a data set. | =LARGE(A2:A6, 2) |
Returns the second-highest score among the five students. Result: 92 |
| Find the value at a specific rank from the top of a data set. | =LARGE(A2:A6, 3) |
Returns the third-highest score among the five students. Result: 88 |
| Find the lowest value in a data set by asking for the last-ranked largest value. | =LARGE(A2:A6, 5) |
Returns the 5th-highest score out of 5 total — which is the same as the lowest. Result: 78 |
| Find the name associated with the highest ranked value in a data set. | =INDEX(B2:B6, MATCH(LARGE(A2:A6, 1), A2:A6, 0)) |
Finds the highest score (95), locates its position among the scores, then returns the matching student name at that position. Result: 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.