How to use LARGE Function in Excel

The Microsoft Excel LARGE Function is the function which is responsible for returning the nth largest value from a given set of values in the spreadsheet. The LARGE Function in Excel is a built-in function of Microsoft Excel and is categorized as a Statistical Excel Function. This function can be entered as a part of the formula in a cell of Excel worksheet. The LARGE in Excel basically returns a numeric value which is totally based on their position in a supplied list of values when sorted. In other words, we can say that the LARGE Function retrieves “nth largest” values-largest value, 2nd largest value, 3rd largest value etc.

Syntax:= LARGE (array, n)

The LARGE function syntax has the following arguments:

• Array    Required. The array or range of data for which you want to determine the k-th largest value.

• K    Required. The position (from the largest) in the array or cell range of data to return.

Example: Let’s look at some Excel LARGE function examples and explore how to use the LARGE function as a worksheet function in Microsoft Excel:

Example 1:

Syntax:  =LARGE($B$2:$B$17,D2)

Result: 90000

Based on the Excel spreadsheet above, the following LARGE examples would return:

Example 2:

Let’s assume Sales data given and we want to see the total amount of sales from the top 5 performers.

We will apply the LARGE function for selecting the top 5 sales performer by passing the positions from 1 to 5 as an array as a second argument (k) position and sum those values.

The formula used for solving this problem is:

Syntax:  =LARGE($B$2:$B$17,D2)

Result: 18500

Note:

1. #VALUE! error – Occurs when the supplied k argument is non-numeric.
2. #NUM error – Occurs when:
   • The array provided is empty.
   • When the value of the k argument is less than 1 or greater than the number of values in the given array.