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:

LARGE Function in Excel 1 - How to use LARGE Function in Excel

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

Result: 90000

LARGE Function in Excel - How to use LARGE Function in Excel

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

Syntax: =LARGE($B$2:$B$17,D3)
Result: 85000

Syntax: =LARGE($B$2:$B$17,D4)
Result: 82000

Syntax: =LARGE($B$2:$B$17,D5)
Result: 80000

Syntax: =LARGE($B$2:$B$17,D6)
Result: 78000

Syntax: =LARGE($B$2:$B$17,D7)
Result: 71000

Syntax: =LARGE($B$2:$B$17,D8)
Result: 70000

Syntax: =LARGE($B$2:$B$17,D9)
Result: 64000

Syntax: =LARGE($B$2:$B$17,D10)
Result: 63000

Syntax: =LARGE($B$2:$B$17,D11)
Result: 62000

Example 2:

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

LARGE Function 1 - How to use LARGE Function in Excel

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

LARGE Function - How to use LARGE Function in Excel

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.

Add a Comment

Your email address will not be published. Required fields are marked *