Excel LARGE Function
Summary
The Excel LARGE function returns numeric values based on their position in a list when sorted by value. In other words, it can retrieve "nth largest" values - 1st largest value, 2nd largest value, 3rd largest value, etc.
Purpose
Get the nth largest value
Return value
The nth largest value in the array.
Syntax
=LARGE (array, n)
Arguments
- array - The array from which you want to select the kth largest value.
- n - An integer that specifies the position from the largest value, i.e. the nth position.
Version
Usage notes
The LARGE function returns numeric values based on their position in a list when sorted by value. In other words, LARGE can retrieve "nth largest" values - 1st largest value, 2nd largest value, 3rd largest value, etc.
The LARGE function is useful when you want to retrieve the nth highest value from a set of data — for example, the first, second, or third highest scores for a test.
To get nth smallest values, see the SMALL function.
Example
To retrieve the 1st, 2nd, and 3rd largest values in a range:
LARGE formula examples
The SMALL function is meant to extract the "nth" smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value,...
The LARGE function is designed to retrieve the top nth value from a set of numbers. So, for example LARGE(A1:A10,1) will return highest value, LARGE(A1:A10,2) will return the 2nd highest value, and so on:
LARGE(range...
The LARGE function is fully automatic — you just need to supply a range and an integer for"nth" to specify the ranked value you want.
The problem in this case is that we don't want LARGE to operate on every value in...
The LARGE function can be used to retrieve "nth" largest value in numeric data like so:
=LARGE(values,n)
In this example, we need include only values associated with group B. To do this, we use the IF function to...
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B.
The FILTER function applies criteria with the...
The LARGE function is fully automatic — you just need to supply a range and an integer for"nth" to specify the ranked value you want. The official names for these arguments are "array" and "k".
For example we can use...
The AVERAGE function will calculate an average of numbers presented in an array, so almost all the work in this formula is to generate an array of the last 3 numeric values in a range. Working from the inside out, the...
In a nutshell, this formula uses the LARGE function to find the nth largest value in a set of data. Once we have that value, we plug it into a standard INDEX and MATCH formula to retrieve the associated name. In other...
This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts.
Note there is no actual...
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.
The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range...
In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula:
=LARGE(B4:B13, 2)
will return the 2nd largest value in the range B4:B13 which, in the example above, is the...
In its simplest form, LARGE returns the "Nth largest" value in a range with this construction:
=LARGE (range,N)
So, for example:
=LARGE (C5:C12,2)
will return the 2nd largest value in the range C5:C12, which is...
The LARGE function is an easy way to get the nth largest value in a range:
=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest
In this example, we can use the LARGE...
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make sure you use absolute references for both of these ranges in...
Related videos
In this video, we'll use the FILTER function, with the LARGE and SMALL functions, to show the top or bottom results in a set of data.
In this video, we'll look at how to calculate minimum and maximum values in Excel, using the MAX and MIN function.
In this video, we'll look at how to calculate the nth smallest or largest values in a range using the SMALL and LARGE functions. This would be for example the 1st, 2nd, and 3rd smallest or largest values.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
