Which of the following function returns the largest integer that is less than or equal to X?

Summary 

The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.

Return value 

The nth largest value in an array.

Arguments 

• array - An array or range of numeric values.
• k - Position as an integer, where 1 corresponds to the largest value.

Usage notes 

The LARGE function returns a numeric value based on its position in a list when sorted by value. In other words, LARGE returns the "nth largest" value in the list where 1 corresponds to the largest value, 2 corresponds to the second largest value, etc. For example, the LARGE function is useful when you want to retrieve first, second, or third highest scores for a test. 

The LARGE function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the largest value in array, provide 1 for k. To return the fifth largest value in array, provide 5 for k.

To get nth smallest values, see the SMALL function.

Examples

In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant:

=LARGE[{29,14,33,19,17},3] // returns 19

Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd largest values in a range:

=LARGE[range,1] // 1st largest value =LARGE[range,2] // 2nd largest value =LARGE[range,3] // 3rd largest value

In the example shown, the formulas in G5, G6, and G7 are, respectively:

=LARGE[D5:D16,1] // returns 92 =LARGE[D5:D16,2] // returns 89 =LARGE[D5:D16,3] // returns 86

See below for more advanced formulas based on the LARGE function.

Notes

• LARGE ignores empty cells, text values, and TRUE and FALSE values.
• If array contains no numeric values, LARGE returns a #NUM! error.
• To determine the rank of a number in a data set, use the RANK function.

LARGE formula examples

In this example, the goal is to sum the largest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three largest values, so n is equal to 3. At a high level, this...

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 for...

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...

In this example, the goal is to sum the largest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps [1] extract the n largest values from...

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...

In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The...

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 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,...

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...

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...

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...

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...

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...

Related videos

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. For example, the 1st, 2nd, and 3rd smallest or largest values.

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.

Related functions 

The Excel MAX function returns the largest numeric value in the data provided. MAX ignores empty cells, the logical values TRUE and FALSE, and text values.

The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value [1st smallest value, 2nd smallest value, 3rd smallest...

The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest [i.e. top sales] as well as smallest to largest [i.e. fastest time].

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel video training

Quick, clean, and to the point.

Learn more