# A to Z of Excel Functions: the LARGE Function

4 September 2021

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the LARGE function. *

** **

**The LARGE function**

Are you living life **LARGE**? This function returns the **k**th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use **LARGE** to return the highest, runner-up, or third-place score. Its syntax is as follows:

**=LARGE(range, k)**

**LARGE **has the following arguments:

**array:**this is required and represents the array or range of data for which you want to determine the**k**th largest value**k:**this is also required. This denotes the position (from the largest) in the array or cell range of data to return.

It should be noted that:

- if
**array**is empty**, LARGE**returns the*#NUM!*error value - if
**k**≤ 0 or if**k**is greater than the number of data points,**LARGE**returns the*#NUM!*error value - 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.

**k **must be a positive integer less than or equal to the number of non-blank items in the **range**. For example,

There are opportunities to create errors using this reasonably straightforward function:

Again, other than choosing an inappropriate **n** (*e.g*. choosing a negative value, too large a value or a non-integer), blank cells may again cause problems. Ensure you do not include blank cells in your given **range**.

**LARGE** may be used to rank numerical data in descending order using the **ROWS **function:

**LARGE** may also be used to derive statistical data from a range, sometimes requiring an array formula and sometimes not (this is often a case of trial and error for the inexperienced). For example, here’s two ways to calculate the sum of the top three (largest) values in the following range:

The formulae

**{=SUM(LARGE(F71:F85,H71:H73))}** *and*

**=SUM(LARGE(F71:F85,{1,2,3}))**

will both sum the top three items in the list. You should note that an array formula is avoided in the second formula as **n** is specified as **{1,2,3}** – effectively creating an array of data without pressing **CTRL + SHIFT + ENTER**.

Similar formulae may be created for the sum of the bottom five, the average of the fourth, eighth and 12^{th} largest items and so on. Try doing that with **MAX** or **MIN**!

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.*

*A full page of the function articles can be found here. *