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.
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
Let’s say we have a list of films and their corresponding box office earnings. Using functions like
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:
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
LARGEfunction. 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.
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:
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
- First, select the cell which will hold the result of our
LARGEfunction. 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
LARGEfunction. 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
MATCHfunctions 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
SMALLfunction 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
LARGEformula will return an error.
This function guide shows how easy it is to return the nth largest element in a dataset.
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.