Note: The other languages of the website are Google-translated. Back to English
English English

Excel LARGE Function

The Excel LARGE function returns the k-th largest number in an array or range of data.

large function 1


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)

large function 2


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.

large function 3


Related functions

Excel MAX Function

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.

Excel SMALL Function

The Excel SMALL function returns the k-th smallest value (1st smallest value, 2nd smallest value, etc.) in an array or range of data.

Excel MIN Function

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.

Excel RANK Function

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.

Excel PERCENTILE.INC Function

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations