A to Z of Excel Functions: The LARGE Function

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 kth 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 has the following arguments:

• array: this is required and represents the array or range of data for which you want to determine the kth 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

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.