Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Large-Function

The Excel LARGE Function

Related Functions:
SMALL Function
MAX Function
MIN Function

Function Description

The Excel Large function returns the k'th largest value from an array of numeric values.

The syntax of the function is:

LARGE( array, k )

where the function arguments are:

array - An array of numeric values from which you want to find the k'th largest value.
k - The index (i.e. the function returns the k'th largest value from the supplied array).

The array argument can be supplied to the function either directly, or as a reference to a range of cells containing numeric values. If values in the supplied range of cells are text values, these values are ignored.


Large Function Examples

The following spreadsheet shows the Excel Large function, used to retrieve the 1st, 2nd, 3rd, 4th and 5th largest values from the set of values in cells A1-A5.

 Formulas:
  A B
1 6 =LARGE( A1:A5, 1 )
2 12 =LARGE( A1:A5, 2 )
3 15 =LARGE( A1:A5, 3 )
4 1 =LARGE( A1:A5, 4 )
5 4 =LARGE( A1:A5, 5 )
 Results:
  A B
1 6 15
2 12 12
3 15 6
4 1 4
5 4 1

Note that in the above example spreadsheet:


Further details and examples of the Excel Large function are provided on the Microsoft Office website.


Large Function Errors

If you get an error from the Excel Large Function, this is likely to be one of the following:

Common Errors
#NUM! -

Occurs if either:

  • The supplied value of k is less than 1 or greater than the number of values in the supplied array
or
  • The supplied array is empty.
#VALUE! - Occurs if the supplied k is non-numeric.

Also, the following problem is encountered by some users:

Common Problem

The Large function returns the wrong value, or returns the #NUM! error, even though the supplied value of k is between 1 and the number of values in the supplied array.

Possible Reason

Text values, including text representations of numbers within the supplied array, are ignored by the Large function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values.

Solution

This problem can be solved by converting all array values into numeric values. For details of how to do this, see the Convert Text to Number page.

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions