How to Use the LARGE Function in Google Sheets
You can use the MAX function to find the largest value in a range. But what if you need to find the 5th, 10th, 4th, 2nd, etc., largest value in a spreadsheet? That’s where the LARGE function comes in. This guide will cover how it works and some practical examples.
Sign up forfree
Forgot your password?
Create an account
*Required: 8 chars, 1 capital letter, 1 number
By continuing, you agree to thePrivacy PolicyandTerms of Use.You also agree to receive our newsletters, you can opt-out any time.
The LARGE formula is used to find the value for the nth largest value in a dataset, which the user will define. The function is easy to use and doesn’t require advanced Google Sheets knowledge. Before we look at examples of how you may use this formula, let’s look at how it works.
Here is the syntax for the LARGE formula in Google Sheets:
The formula uses two parameters. The first parameter is thedatasetused to define an array or a cell range that contains the data you want to search through. The second parameter is thenumberused to define the rank of the largest number to return. For example, using 6 as the number parameter means that the function will find the 6th largest element in the data set.
Here are three ways to use the LARGE function within ranges or arrays:
1. In a One Dimensional Array
In this example, we have a one-dimensional array that contains 15 values. We want to find the data set’s 3rd, 5th, and 6th largest values for this demonstration. Let’s look at how you could do this in a spreadsheet. Follow along with the steps below:
Google Sheets will now go through the data and find the nth number value of the largest number in the data set.
2. In a Two-Dimensional Array
Below, we have a two-dimensional array that contains 18 values. For this demonstration, we want to find the 2nd largest value in the data set. Let us look at how you may do this in your spreadsheet.
It works similarly to the first example; only you have to add values that spread over several columns as thedatasetargument. Here are the steps you would need to follow:
3. Using Single Cell Array Literals
Often, you won’t want to display the data in a table in Google Sheets, but you may still want to find the nth largest number in a data set. The LARGE function can also be used with single cellsarray literals in Google Sheets. This allows you to add and delete the values in the specified array. This means you won’t have to execute the formula repeatedly. Rather, it’s possible to simply edit the values, and the formula will do the calculations automatically.
The example below shows six random numbers in a single cell array. We want to find the 3rd largest value in this dataset. Here are the steps you would need to follow to use LARGE in your spreadsheet for this type of calculation:
You can also apply the LARGE function as part of anARRAYFORMULA in Google Sheets.
Things to Know About the LARGE Function
When Not to Use LARGE in Google Sheets
If text values are in the range, it can break the formula, so you could be better off using MAX instead.
Another situation where you should not use LARGE is when attempting to determine the median of an even range of values. The median is the midpoint of a set of numbers and is an important part ofcalculating standard deviation in Google Sheets. But when it is calculated with the LARGE function, you need to put the central position as the number argument.
For example, you need to use 3 as the number argument in a set of 5 numbers to find the median with the LARGE function. There is no central number in a data set with an even amount of numbers, and the median would be the average of the two central numbers. Instead, you should use the MEDIAN function.
Formulas That Are Similar to LARGE in Google Sheets
Here are a few similar functions that should come easy now that you’ve learned about LARGE:
LARGE vs. MAX Function
With LARGE, you can specify the value’s rank, such as first highest, second highest, and so on. The MAX function can return only the first largest element in the range.
The LARGE function returns a “#NUM! error” when applied to a range of only text values. However, regardless of the values in the range, you can still use the MAX function. MAX will return 0 if the range contains only text values.
A LARGE Amount of Learning
Although using LARGE on its own is pretty straightforward, functions in Google Sheets are often combined to make more complex formulas. It’s a good idea to familiarize yourself with as many functions as possible, so you can tackle these more difficult spreadsheet problems.
Google Sheets is one of the best spreadsheet apps that you access via your web browser. Here are some tips to help you get started.
You don’t need to fork out for expensive hardware to run an AI on your PC.
Unlock a world of entertainment possibilities with this clever TV hack.
Free AI tools are legitimately powerful; you just need to know how to stack them.
This small feature makes a massive difference.
Now, I actually finish the books I start.
