LARGE(array, k)
Returns the k-th largest value in a data set
Excel LARGE function
The LARGE function in Excel is used to return the n-th largest value from a numeric data set. For example, it can calculate the highest score, the 2nd largest order, the 3rd place result, and so on.
The syntax consists of two argument, both of which are required:
LARGE(array, k)
Where:
- Array - a range or an array where to search for the largest value.
- K - the position from the highest to return, i.e. k-th largest value in a dataset.
LARGE is categorized under Statistical functions. It is available in all versions of Excel for Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and earlier.
3 things to know about LARGE function
Before we get to more practical things and start building our own formulas, please pay attention to 3 simple facts that explain the essentials:
- The LARGE function processes only numeric values. Blank cells, text, and logical values are ignored.
- If array contains any errors, an error is returned.
- In case array contains n values, LARGE(array,1) will return the maximin value, and LARGE(array,n) will return the minimum value.
Basic LARGE formula
In its basic form, a LARGE formula in Excel is very easy to build. For the 1st argument, you supply a range of numeric values. In the 2nd argument, you define the position from largest to return.
In the sample table below, supposing you wish to know the 2nd largest score. This can be done with the following formula:
=LARGE(B2:B10, 2)
How to use LARGE formula in Excel - examples
And now, let's look at more specific use cases and see how the LARGE function could be helpful.
How to get top N values in Excel
How to sum or average largest N values
Get data associated with the n-th largest value
LARGE formula to sort numbers descending
LARGE formula for dates and times
Get a future date closest to today or specified date
Excel LARGE function not working
A LARGE formula may throw a #NUM! error because of the following reasons:
- The supplied array is empty or does not contain a single numeric value.
- The k value is a negative number.
- The k value is greater than the number of values in array.
That's how to use the LARGE function in Excel to find highest values in a dataset. I thank you for reading and hope to see you on our blog next week!