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

**Overview of Excel LARGE Function:**

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

**Table of Contents**hide

## Download Practice Workbook

**Introduction to Excel LARGE Function**

**Summary**

Returns the K-th largest value in a dataset where K must be a positive integer.

**Syntax**

`=LARGE(array, k)`

**Arguments**

ARGUMENT |
REQUIREMENT |
DESCRIPTION |
---|---|---|

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)** **LARGE(array,1)**returns the biggest value and**LARGE(array,n)**returns the smallest value if n is the number of data points in a range.- The
**LARGE**function is suitable for numeric values only. It ignores the text, blank cells, and logical values.

**7 Examples to Use LARGE Function in Excel**

Now, In this section, I will give 7 examples that will help you understand the application of this function in detail. So, let’s get started with our first example.

### 1. Use of LARGE Function to Get Top N Values in Excel

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.

To do that, follow the steps below.

**Steps:**

- Enter the formula in cell
**E16**and copy it down up to**E18**cell.

`=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 where the LARGE function will search values. In this cell **D16**, we have passed the position of the searching elements.

### 2. Combining AVERAGE & LARGE Functions in Excel to Average Largest N-Values

For this example, let’s assume we need to find out the **average CGPA** of the top 4 students’ GPAs.

We can find this using Excel’s **LARGE**,** **and **AVERAGE **functions. Follow the steps below.

**Steps:**

- 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.

### 3. Combining SUM & LARGE Functions in Excel to SUM Largest N-Values

For this example, let’s assume we need to find out the sum of the top 4 students’ GPAs.

We can find this using Excel’s **LARGE **& **SUM **functions. Follow the steps below.

**Steps:**

- And enter the following formula in
**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.

### 4. Utilizing INDEX, MATCH & LARGE Functions in Excel to Get Associated Data

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 the help of combining the **LARGE **function with **the INDEX & MATCH Functions**.

To learn more, follow the steps below.

**Steps:**

- Enter the formula in cell
**E16**and copy it down to**E18.**You will get the following result.

`=INDEX($B$5:$B$12, MATCH(LARGE($F$5:$F$12, $D16), $F$5:$F$12, 0))`

**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.

**Similar Readings**

**How to Use COUNTIFS Function in Excel (4 Examples)****The Different Ways of Counting in Excel****How to Use COUNT Function in Excel (With 5 Examples)****Use COUNTA Function in Excel (3 Suitable Examples)**

### 5. Combining ROWS & LARGE Functions in Excel to Sort Numbers in Descending Order

Let’s think we need to sort the CGPA of the students in a separate column (**Sorted CGPA).**

We can do this using **ROWS **and **LARGE **functions easily. Let’s follow the steps below.

**Steps:**

- Enter the formula
**H5**and copy it down up to**H12.**

`=LARGE($F$5:$F$12, ROWS(F$5:F5))`

**How Does the Formula Work?**

**ROWS(F$5:F5)**

This part of the formula returns the rows number of rows in the range.

**LARGE($F$5:$F$12, ROWS(F$5:F5))**

Lastly, the **LARGE **function finds all the large numbers according to the row serial from this **$F$5:$F$12** range.

### 6. Use of LARGE Function to Find Nearest Dates

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.

To accomplish that, follow the steps below.

**Steps:**

- Enter the formula in cell
**D15**and copy it down up to**D17**cell.

`=LARGE($D$5:$D$12, ROWS(D$5:D5))`

### 7. Use the LARGE Function to **Get an Upcoming Date Nearest to Today or a Specified Date**

Now in this section, we find out the upcoming 3 graduation dates. For me, today is 9th November 2022. Now we will find the top 3 three dates which are near to the current date.

To do that, follow the steps below.

**Steps:**

- Enter the following three 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()))**

Finally, the **LARGE **function is used to find the largest dates.

**Similar Readings**

**How to Use COUNTIF Function in Excel (10 Suitable Applications)****Use TODAY Function in Excel (6 Easy Examples)****How to Use RANK Function in Excel (With 5 Examples)**

**When Will LARGE Function Not Work in Excel?**

This **LARGE** function will not work 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 ARE SHOWN |
---|---|

#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

**How to use MAX function in Excel (6 Examples)****Use SMALL Function in Excel (4 Common Examples)****How to use COUNTBLANK function in Excel (3 Examples)****Use MIN Function in Excel (5 Relevant Examples)****How to Use VAR Function in Excel (4 Examples)****Use MODE Function in Excel (4 Examples)****How to Use AVERAGEIFS Function in Excel (4 Examples)**