# How to Use LARGE Function in Excel (6 Easy Examples) When we need to find a specific largest value, like the 2nd or the 3rd biggest number in any dataset. The Excel’s LARGE function returns numeric values based on their position in a list when sorted by value. This article will share the complete idea of how the LARGE function works in Excel autonomously and then with other Excel functions.

COUNT Function in Excel (Quick View) ## Excel LARGE Function: Syntax & Arguments Summary

Returns the K-th largest value in a dataset. Like the third-largest number.

Syntax

`=LARGE (``array``, ``k``)`

Arguments

Argument Required or Optional Value
array Required The array from which you need to choose the kth largest value.
k Required Pass an integer that specifies the position from the largest value, as the nth position.

Note:

• Here the value of K should be greater than 0. (K>0)
• If n is the number of data points in a range, then LARGE(array,1) returns the largest value, and LARGE(array,n) returns the smallest value.
• The LARGE function processes only numeric values. Blank cells, text, and logical values are ignored.

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

### Example 1: How to get the top N values in Excel Using LARGE Function

Let’s have a dataset of some students with their Name, Department, Admission date, Graduation date, and CGPA. From this dataset, we will find out the top 3 results using the LARGE function. Step 1: Enter the formula in cells E16 and copy it down up to E18

`=LARGE(\$F\$4:\$F\$11, D16)`

Formula Explanation

• \$F\$4:\$F\$11 this is the range where the LARGE function will search values.
• In this cell D16, we have passed the position of the searching elements. Let’s 1 means it will fund the first largest values from the range. ### Example 2: How to sum or average the largest N values Using LARGE Function

For this example, let’s assume we need to find out the average CGPA of the 4 students and the total of the top 4 students’ GPAs. We can find this using Excel’s LARGE, SUM, and AVERAGE functions. Step 1: Enter the formulas in cells D15 and D18

`=AVERAGE(LARGE(F4:F11, {1,2,3,4}))`

And

`=SUM(LARGE(F4:F11, {1,2,3,4}))`

Formula Explanation

• 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 value using array argument.
• The AVERAGE function calculates the average of the selected values, and the SUM function returns the summation. ### Example 3: Get Data Associated with the N-th Largest Value Using LARGE Function

By default, we can just only extract the numerical value using the LARGE function. But sometimes we may need to find the associated data with the largest value in the nth position. In this section, we will see how to find the top 3 student names with help of the LARGE function. Step 1: Enter the formula in cell E16 and copy it down up to E18

`=INDEX(\$B\$4:\$B\$11, MATCH(LARGE(\$F\$4:\$F\$11, \$D16), \$F\$4:\$F\$11, 0))`

Formula Explanation

• MATCH(LARGE(\$F\$4:\$F\$11, \$D16), \$F\$4:\$F\$11, 0) this portion of the formula finds the matched data for the N-th largest CGPA. LARGE(\$F\$4:\$F\$11, \$D16) this is our  lookup_value,\$F\$4:\$F\$11 this is our lookup_array, and  0 is our  match_type which will find the exact match. If you want to know more about the MATCH function you can visit this link
• Lastly, the INDEX function will find the associated data with the largest values. \$B\$4:\$B\$11 this is the range where we will find the associated data and the row will be selected using the MATCH and LARGE functions. If you want to know more about the INDEX function you can visit this link ### Example 4: LARGE Function Formula to Sort Numbers in Descending Order

Let’s think we need to sort the CGPA of the students in a separate column. We can do this using ROWS and LARGE functions easily. Step 1: Enter the formula C15 and copy it down up to C22

`=LARGE(\$F\$4:\$F\$11, ROWS(F\$4:F4))`

Formula Explanation

• ROWS(F\$4:F4) this part of the formula returns the rows number of rows in the range. If you want to know more about the ROWS function you can check this link
• Lastly, the LARGE function finds all the large numbers according to the row serial from this \$F\$4:\$F\$11 range. ### Example 5: LARGE Formula for Dates and Times

Using the LARGE and ROWS functions we can find the recent dates. Let’s say we want to find the most recent 3 student’s admission dates. Step 1: Enter the formula in cell D15 and copy it down up to D17

`=LARGE(\$D\$4:\$D\$11, ROWS(D\$4:D4))` ### Example 6: Get a Future Date Closest to Today or a Particularized Date

Now in this section, we find out the upcoming 3 graduation dates. For me, today is 12 August 2021. Now we will find the top 3 three dates which are near to may date. Step 1: Enter the formula in cells D16, D17, and D18

`=LARGE(\$E\$4:\$E\$11, COUNTIF(\$E\$4:\$E\$11, ">"&TODAY()))`

And

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

And

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

Formula Explanation

• COUNTIF(\$E\$4:\$E\$11, “>”&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:
How to Use COUNTIF Function in Excel (10 Suitable Applications)
How to Use TODAY Function in Excel (6 Easy Examples)
• COUNTIF(\$E\$4:\$E\$11, “>”&TODAY())-1 and COUNTIF(\$E\$4:\$E\$11, “>”&TODAY())-2 this used to find the second and third most upcoming dates.
• Finally, the LARGE function is used to find the largest dates. ## When LARGE Function will not Work

This LARGE function will not for the following circumstances:

• If the k value is a negative number.
• If the k value is higher than the number of values in an array.
• The provided array is empty or does not include a single numeric value.

## Things to Remember

Common Errors When they show
#NUM! This error will appear if the array is empty. Also if k ≤ 0 or if k is greater than the number of data points.
#VALUE! This error will appear if the supplied K is a non-numeric value.

## Conclusion

That’s it all about LARGE function. Here I have tried to give a piece of proper knowledge about this function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any inquiries or feedback, please let us know in the comment section.  