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

**Read More:** How to Lookup Next Largest Value in Excel

### 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))`

**Read More:** How to Find Second Largest Value with Criteria in Excel

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

## Frequently Asked Questions

**Alternative Functions:**- If you want to find the largest value in a range without specifying a position, you can use the
**MAX**function.

- If you want to find the largest value in a range without specifying a position, you can use the
**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.

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

- Yes, you can use the

**Download Practice Workbook**

You can download the practice workbook from here:

## Excel LARGE Function: Knowledge Hub

**<< Go Back to Excel Functions | Learn Excel**