The LARGE function in Google Sheets is **useful when you need to find out the nth largest element from a data set.**

For example, given a dataset of size 100, we can use the LARGE function to find the 10th largest, 50th largest, or 75th largest element in the dataset.

##### Table of Contents

The rules for using the `LARGE`

function in Google Sheets are as follows:

- The function requires two arguments. The first argument is an array or range which holds our dataset. The second argument is the rank of the output to return.
- The function then outputs the nth largest element in the provided dataset.

Let’s look at a scenario where we can use the `LARGE`

function.

Let’s say we have a list of films and their corresponding box office earnings. Using functions like `MIN`

and `MAX`

, we can get the highest-grossing and lowest-grossing films on that list. But what about for cases when we need to know the 2nd, 3rd, or 10th highest-grossing movie? The `LARGE`

function makes it easy to get the nth largest element given a dataset with numerical values.

This use case is just one possible use case for the `LARGE`

function in Google Sheets. The function can be useful to get the nth element of any given range as long as it has numerical data.

Let’s learn how to set up the `LARGE`

function ourselves in Google Sheets. Later, we’ll test out the function with sample numerical values.

**The Anatomy of the LARGE Function**

The syntax of the `LARGE`

function is as follows:

=LARGE(data, n)

Let’s examine this formula to understand what each of these terms means:

**=**the equal sign is how we start any function in Google Sheets.**LARGE()**is our`LARGE`

function. It computes the nth largest element in a dataset.**data**refers to the array or range to consider when using the function.**n**refers to the rank (from largest to smallest) of the element to return.

**A Real Example of Using LARGE Function**

Let’s look at a real example of the `LARGE`

function being used in a Google Sheets spreadsheet.

In the table below, we have box office data of the top 25 movies from 1999. The films are arranged alphabetically, but we have a breakdown of each film’s worldwide, domestic, and international box office figures.

Using the `LARGE`

function, we were able to set up a separate table that pulls up the box office of any arbitrary nth highest-grossing movie worldwide.

To fill in the values of worldwide box office, we just need to use the following formula:

=LARGE($B$2:$B$26,G3)

The first argument is the range of our worldwide box office column. The second argument is the value of n, which will indicate that the function will return the nth largest number in the range.

You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to try out the `LARGE`

function in Google Sheets, let’s begin writing it ourselves!

**How to Use LARGE Function in Google Sheets**

In this section, we will go through each step to start using the `LARGE`

function in Google Sheets. This guide will show you how to use the function to find the fifth highest-grossing movie of 1999 and so on.

Follow these steps to start using the `LARGE`

function:

- First, select the cell which will hold the result of our
`LARGE`

function. In this example, we’ll start with cell**H2**.

- Next, we just need to input the equal sign ‘
**=**‘ to begin the function, followed by ‘**LARGE(**‘. - You may notice a tooltip box with hints on how to use the
`LARGE`

function. Click on the arrow symbol on the top-right-hand corner of the box to minimize it if needed.

- Next, we need to type in our arguments. In the table below, we selected the range that holds the worldwide box office numbers and the cell with the value of n. Afterward, hit
**Enter**on your keyboard to let the function return the nth largest element.

- We can drag down the formula to fill out the rest of the column.

- Additionally, we can use the
`INDEX`

and`MATCH`

functions to get the film that corresponds to the returned box office value.

**Frequently Asked Questions (FAQ)**

**Can the LARGE function return the nth smallest element instead?**

The Large function cannot provide the nth smallest element for a user-defined value n. You may use the`SMALL`

function instead to achieve this task.**Why does my formula return a #NUM! error?**

The value of n must be between 1 and the size of the dataset. If the value is outside this range, the`LARGE`

formula will return an error.

This function guide shows how easy it is to return the nth largest element in a dataset.

The `LARGE`

function is just one example of a useful function in Google Sheets to work with values in a dataset. With so many other Google Sheets functions out there, you can surely find the functions you need to create the best spreadsheets.

Don’t forget to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.