How to use LARGE Google Sheets formula?

The syntax for the LARGE formula is =LARGE(data, n), it has 2 required parameters.

LARGE

The LARGE formula is a statistical function that returns the nth largest value in a range or array. It is often used to find the top or bottom values in a dataset or to determine the nth highest or lowest value. The function can be used in combination with other formulas to perform more complex calculations.

How to use LARGE formula?
Examples of using LARGE formula
LARGE formula not working?
Similar formulas to LARGE

Usage

Use the LARGE formula with the syntax shown below, it has 2 required parameters:

=LARGE(data, n)

Parameters:

data (required):
The range or array of data for which to find the nth largest value.
n (required):
The position of the value to return from the sorted data. For example, if n is 1, the formula will return the largest value in the range or array. If n is 2, the formula will return the second largest value, and so on.

Examples

Here are a few example use cases that explain how to use the LARGE formula in Google Sheets.

Find the highest value in a range

To find the highest value in a range, use the LARGE function with n set to 1. For example: =LARGE(A1:A10,1) will return the highest value in cells A1 through A10.

Find the second highest value in a range

To find the second highest value in a range, use the LARGE function with n set to 2. For example: =LARGE(A1:A10,2) will return the second highest value in cells A1 through A10.

Find the lowest value in a range

To find the lowest value in a range, use the LARGE function with n set to the number of values in the range. For example: =LARGE(A1:A10,COUNT(A1:A10)) will return the lowest value in cells A1 through A10.

Common Mistakes

LARGE not working? Here are some common mistakes people make when using the LARGE Google Sheets Formula:

Using non-numeric data in the data parameter

The data parameter in the LARGE formula should only contain numeric values. If there are non-numeric values in the data parameter, the formula will return an error. To fix this, remove any non-numeric data from the data parameter.

Entering a non-positive value for n

The n parameter in the LARGE formula should be a positive integer. If a non-positive integer is entered, the formula will return an error. To fix this, enter a positive integer for the n parameter.

Not selecting a large enough range for the data parameter

The data parameter in the LARGE formula should include all the data that you want to find the nth largest value for. If the range selected is not large enough, the formula will return an error. To fix this, select a larger range for the data parameter.

The following functions are similar to LARGE or are often used with it in a formula:

SMALL
The SMALL function is a statistical function that returns the nth smallest value in a range or array of data. It is commonly used to find the lowest or highest values in a set of data.
AVERAGE
The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.
STDEV
The STDEV function calculates the standard deviation of a set of numbers. It measures the amount of variation or dispersion of a set of values from the average (mean) value. It is commonly used in statistics to determine the spread of a data set. The values can be supplied as individual cells, ranges, or constants.
SUMIF
The SUMIF formula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.

Learn More

You can learn more about the LARGE Google Sheets function on Google Support.