Excel - Microsoft Excel LARGE Function

Description

The Excel LARGE function returns the k'th largest value from an array or a range of cells containing numerical values. 

The format of the function is : 

LARGE( array, k ) 

where the function arguments are: 

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 example shows the Excel Large function, being used to retrieve the 1st, 2nd, 3rd, 4th and 5th largest values from the set of values in cells A1 - A5. 

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right. 

Note that, any calls to the Large function, in which the index, k is set to 1, gives the same result as the Max function. 

In the example above, the functions in cells B1 and B5 could be replaced by the Max function and the Min function, respectively. 

Large Function Error

If you get an error from the Excel Large Function, this is likely to be the #NUM! error: 

Common Error 

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 you believe your 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 (read more about Excel data types on the Excel Formatting page) 

Solution 

Note that the Text to Columns only converts values, it will not change the data type of a cell containing a function.