# How to Use the LARGE Function in Excel (7 Examples)

Overview of Excel LARGE Function

In the following picture, you can see the overview of the LARGE function.

• Summary

The LARGE function returns the K-th largest value from a dataset, where K must be a positive integer.

• Syntax
`=LARGE(array, k)`
• Arguments
ARGUMENT REQUIREMENT DESCRIPTION
array Required The range of values from which you want to find the K-th largest value.
k Required An integer specifying the position (n) of the largest value.

Note:

• K should be greater than 0.
• LARGE(array,1) returns the largest value and LARGE(array,n) returns the smallest value if there are n data points in the range.
• This function works only with numeric values, ignoring text, blank cells, and logical values.

### Example 1 – Top N Values Using LARGE

Suppose we have a dataset of students with their CGPA. To find the top 3 results using LARGE, follow these steps:

• Enter the formula in cell E16 and copy it down to E18:
`=LARGE(\$F\$5:\$F\$12, D16)`

How Does the Formula Work?

• LARGE(\$F\$5:\$F\$12, D16)

Here, \$F\$5:\$F\$12 is the range, and D16 specifies the position of the searching elements.

Read More: How to Find Largest Number in Excel

### Example 2 – Average Largest N CGPA

To find the average CGPA of the top 4 students, combine LARGE and AVERAGE:

• Enter the following formula in cell D15.
`=AVERAGE(LARGE(F5:F12, {1,2,3,4}))`

How Does the Formula Work?

• LARGE(F4:F11, {1,2,3,4})

This portion will find the top 4 largest values from the CGPA dataset. {1,2,3,4} this is used to define the top 4 values using an array argument.

• AVERAGE(LARGE(F5:F12, {1,2,3,4}))

The AVERAGE function calculates the average of the selected values.

### Example 3 – Sum of Largest N CGPA

To calculate the sum of the top 4 students’ GPAs:

• Enter this formula in cell E15:
`=SUM(LARGE(F5:F12, {1,2,3,4}))`

How Does the Formula Work?

• LARGE(F4:F11, {1,2,3,4})

This portion will find the top 4 largest values from the CGPA dataset. {1,2,3,4} this is used to define the top 4 values using an array argument.

• SUM(LARGE(F5:F12, {1,2,3,4}))

The SUM function returns the summation.

### Example 4 – Associated Data with Largest Value

Sometimes we need associated data with the largest value. Use INDEX, MATCH, and LARGE:

• Enter this formula in cell E16 and copy it down to E18:
`=INDEX(\$B\$5:\$B\$12, MATCH(LARGE(\$F\$5:\$F\$12, \$D16), \$F\$5:\$F\$12, 0))`

This will return the student names corresponding to the largest CGPA values in column F.

How Does the Formula Work?

• LARGE(\$F\$5:\$F\$12, \$D16)

This portion of the formula finds the highest (D16=1) CGPA in the F5:F12 range.

• MATCH(LARGE(\$F\$5:\$F\$12, \$D16), \$F\$5:\$F\$12, 0)

This portion of the formula provides the row number of the top CGPA holder in the F5:F12 column.

• INDEX(\$B\$5:\$B\$12, MATCH(LARGE(\$F\$5:\$F\$12, \$D16), \$F\$5:\$F\$12, 0))

Lastly, the INDEX function will return the associated data with the largest value from \$B\$5:\$B\$12 column.

### Example 5 – Sorting Numbers in Descending Order

Suppose we want to sort students’ CGPA in a separate column called Sorted CGPA.

We can achieve this using the ROWS and LARGE functions. Follow these steps:

• Enter the formula in cell H5 and copy it down to H12:
`=LARGE(\$F\$5:\$F\$12, ROWS(F\$5:F5))`

How Does the Formula Work?

• ROWS(F\$5:F5)

Returns the row number within the range.

• LARGE(\$F\$5:\$F\$12, ROWS(F\$5:F5))

Finds the largest numbers from the \$F\$5:\$F\$12 range.

### Example 6 – Finding Nearest Dates

To find the most recent admission dates for the top 3 students, use the LARGE and ROWS functions:

• Enter the formula in cell D15 and copy it down to D17:
`=LARGE(\$D\$5:\$D\$12, ROWS(D\$5:D5))`

### Example 7 – Upcoming Dates Nearest to Today

Suppose today is November 9, 2022. To find the top 3 graduation dates near the current date:

• Enter the following formulas in cells D16, D17, and D18 respectively:
`=LARGE(\$E\$5:\$E\$12, COUNTIF(\$E\$5:\$E\$12, ">"&TODAY()))`

And,

`=LARGE(\$E\$5:\$E\$12, COUNTIF(\$E\$5:\$E\$12, ">"&TODAY())-1)`

And,

`=LARGE(\$E\$5:\$E\$12, COUNTIF(\$E\$5:\$E\$12, ">"&TODAY())-2)`

How Does the Formula Work?

• COUNTIF(\$E\$5:\$E\$12, “>”&TODAY())

This part will count the number of cells using the condition. The condition is the date must be greater than today. Today’s date is found using the TODAY function. To know more about TODAY and COUNTIF functions, you can check these two articles:

• LARGE(\$E\$5:\$E\$12, COUNTIF(\$E\$5:\$E\$12, “>”&TODAY()))

The LARGE function is used to find the largest dates.

## When Will the LARGE Function Not Work in Excel?

This LARGE function will not work under the following circumstances:

• The k value is negative.
• The k value exceeds the number of values in the array.
• The provided array is empty or lacks numeric values.

## Things to Remember

COMMON ERRORS WHEN THEY ARE SHOWN
#NUM! This error occurs if the array is empty or if the value of k (position) is less than or equal to 0, or greater than the number of data points.
#VALUE! This error appears if the supplied k is a non-numeric value.

1. Alternative Functions:
• If you want to find the largest value in a range without specifying a position, you can use the MAX function.
2. Handling Duplicate Values:
• The LARGE function can handle arrays with duplicate values. It treats duplicate values as separate entries and retrieves the kth largest value accordingly.
3. Using LARGE Across Worksheets:
• Yes, you can use the LARGE function with a range of cells in a different worksheet. Specify the worksheet name along with the cell range. For example, use ‘Sheet2!A1:A10’ to refer to cells A1 to A10 in Sheet2.

## Excel LARGE Function: Knowledge Hub

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!