LARGE

The LARGE function in Excel returns the nth largest value from a range of data. It is useful for quickly identifying the top values within a dataset or array.

Syntax

=LARGE(array, n)

array The range or array of data from which you want to find the nth largest value.
n The position (rank) of the largest value you want to return. It must be greater than or equal to 1.
Remark n^th^ largest isn't n^th^ value

About LARGE

When you need to pinpoint the highest values within a set of data in Excel, the LARGE function steps in to simplify the process. It allows you to swiftly extract the nth largest value from a specified array or range, which proves valuable in various analytical scenarios where identifying top results is crucial. Whether you're analyzing sales figures, test scores, or any other numerical data, LARGE facilitates the quick identification of significant data points within your dataset. Simply provide the array containing your data and specify the position of the desired value, and LARGE does the rest by returning the nth largest value to aid your analysis.

Examples

Suppose you have a dataset of exam scores in cells A1:A10 and you want to find the 3rd largest score. The formula would be: =LARGE(A1:A10, 3)

Suppose you have a range of sales revenue in cells B2:B20 and you need to determine the 5th largest revenue amount. The formula to use: =LARGE(B2:B20, 5)

Questions

What happens if the 'n' value in the LARGE function is less than 1?

If the 'n' value in the LARGE function is less than 1, Excel returns a #NUM! error indicating an invalid argument.

Can the LARGE function be used with non-numeric data?

No, the LARGE function is designed to work with arrays or ranges containing numerical values. If non-numeric data is present in the specified array, Excel will return a #NUM! error.

How does the LARGE function handle ties or duplicate values in the dataset?

If there are ties or duplicate values in the dataset, the LARGE function will return the highest occurrence of the nth largest value. Subsequent identical values are not considered when determining the nth largest value.

Related functions

SMALL
MAX
MIN
COUNTIF
RANK