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 retrive "nth largest" values - largest value, 2nd largest value, 3rd lagest 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.
Usage notes
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. Like the SMALL function, the LARGE function retrieves numeric values based on their position in a list when sorted by value.
Note: Microsoft documentation refers to "k" instead of "n". We use n because "nth" is much easier to say.
LARGE formula examples
To dynamically sort a list of numbers in ascending order, you can a simple formula based on the SMALL function with an expanding range. In the example shown, the formula in cell C5 is:
=SMALL(data,ROWS($B$5:B5))...
To sum the top n values in a range matching criteria, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), range represents a range of...
To average the top 3 scores in a data set, you can use a formula based on the LARGE function.
In the example shown, the formula in G6 is:
=AVERAGE(LARGE(B6:F6,{1,2,3}))
How this formula works
The LARGE function can...
To highlight the highest values in a set of data with conditional formatting, you can use a formula based on the LARGE function.
In the example shown, the formula used for conditional formatting is:
=B4>=LARGE(...
To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, from a set of data, you can use the LARGE function.
In the example shown, the formula in J5 is:
=LARGE($C5:$G5,2)
How this formula works...
To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example shown, the formula in D6 is...
To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...
To return the largest values in a set of data with criteria, you can use the a formula based on the LARGE and IF functions.
In the example shown, the formula in F5 is:
{=LARGE(IF(B5:B11="B",C5:C11),2)}...
To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, where each value matches supplied criteria, you can use an array formula that uses the LARGE and IF functions.
In the example shown, the...
Related videos
In this video, we look at how to use the SMALL and LARGE functions to get the nth value from a data set. You can use these functions to get the top 3 test scores, top 5 fastest times, and so on.
Related functions
Good links
