How to Use LARGE Function in Excel (6 Easy Examples)

Overview of LARGE Function

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.

Download the Practice WorkBook

COUNT Function in Excel (Quick View)

Overview of LARGE Function

Excel LARGE Function: Syntax & Arguments

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.

How to get the top N values in Excel Using 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.

Enter formula using LARGE Function

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.

How to sum or average the largest N values Using LARGE Function

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.

Formula using SUM AVERAGE and LARGE Function

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.

Get Data Associated with the N-th Largest Value Using 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

Enter formula using INDEX MATCH and LARGE function

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.

LARGE Function Formula to Sort Numbers in Descending Order

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.

Enter formula using LARGE and ROWS function

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.

 LARGE Formula for Dates and Times

Step 1: Enter the formula in cell D15 and copy it down up to D17

=LARGE($D$4:$D$11, ROWS(D$4:D4))

Enter formula using LARGE and ROWS

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.

Get a Future Date Closest to Today or a Particularized 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.

Enter formula using LARGE COUNTIF and TODAY function

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.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo