Excel LARGE Function
The Excel LARGE function returns the k-th largest number in an array or range of data.
Syntax
=LARGE(array, k)
Arguments
- array (required): The array or range of data from which to retrieve the k-th largest number.
- k (required): An integer that specifies the position from the highest to return.
Return Value
The LARGE function returns a numeric value.
Function Notes
- LARGE ignores empty cells, text, and logical values.
- If array contains any errors, the first error will be returned.
- LARGE returns the #NUM! error value if:
- array contains less numeric values than k;
- k ≤ 0.
- In the case that an array contains n values, LARGE(array,1) will return the maximun value from the array, and LARGE(array,n) will return the minimum value.
Example
To get the 5th largest number from the table as shown below, please copy or enter the formula below in a blank cell, and press Enter to get the result:
=LARGE(B3:C10,5)
Example to retrieve top 5
To get the top 5 numbers from the table, you can add a rank column next to the result column for convenience as shown below. So that you can reference the rank that you want to retrieve its corresponding number, instead of entering the k argument (rank) yourself. After that, please copy or enter the formula below in the top cell (cell F4), and press Enter to get the result:
=LARGE(B3:C10,E4)
√ Note: After entering the formula, drag the fill handle down to apply the formula to the below cells.
Related functions
The Excel MAX function returns the largest value in the supplied set of values. It ignores empty cells, logical values (TRUE and FALSE), and non-numeric values.
The Excel SMALL function returns the k-th smallest value (1st smallest value, 2nd smallest value, etc.) in an array or range of data.
The Excel MIN function returns the smallest value in the supplied set of values. It ignores empty cells, logical values (TRUE and FALSE), and non-numeric values.
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest as well as smallest to largest.
The Excel PERCENTILE.INC function returns the k-th percentile of the values in a given range. The k is from 0 to 1, including 0 and 1.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
