LARGE Excel Function

The Microsoft Excel LARGE function is the function that is responsible for returning the nth largest value from a given set of values in the spreadsheet. The LARGE function in Excel is a built-in function of Microsoft Excel. It is categorized as a Statistical Excel function. This function can be inserted as a part of the formula in an Excel worksheet cell. The LARGE in Excel returns a numeric value based on their position in a supplied list of values when sorted. In other words, we can say that the LARGE function retrieves “nth largest” values ---largest values, the second largest value, the third-largest value, etc.

For example, LARGE(A1:A5,1) will return the largest value. At the same time, LARGE(A1:A5,2) will return the second-largest value, etc.

The LARGE function accepts the following parameters and arguments:

• array – This is the range or array from which you want the function to return the nth largest value.
• nth_position – This integer specifies the position from the value, the largest, the nth position.

The return value will be numeric, the nth largest value in the array. Please note that if the nth_position is a larger value than the number of values in the array, the LARGE Excel function will return #NUM! Error.   If the supplied array is empty, the LARGE function may return #NUM! Error.

• The LARGE function in Excel is useful when retrieving the nth highest value from the supplied set of data.
• For example, we can use the LARGE function to find the first, second, or third-highest test scores.
• Like the SMALL Function in Excel, the LARGE Excel function also retrieves numeric values based on their position in a supplied list when sorted by value.
• Please note that Microsoft Excel uses “k” instead of “n.” We are using “nth” because it is much easier to understand the LARGE formula in Excel, and it is working.

Let us look below at some examples of the LARGE function in Excel. These examples will help you in exploring the use of LARGE in Excel.

Based on the above Excel spreadsheet, let us consider these examples and see the LARGE function return based on the function's syntax.

Consider the screenshots of the above examples for a clear understanding.

Apply the LARGE formula in Excel =LARGE(A1:A5, 1), and we get 48

Use the LARGE formula in Excel: =LARGE(A1:A5, 2) to get 9.

Apply the LARGE formula here:  =LARGE(A1:A5, 3) to get 6.9

Now, apply the LARGE formula in Excel here: =LARGE(A1:A5, 4) to get 5

Here we apply the LARGE formula in Excel: =LARGE(A1:A5, 5) to get -3.7

Here, we use the formula to calculate the LARGE in Excel: =LARGE((6, 23, 5, 2.3), 2)

We can use the Microsoft Excel LARGE function for various purposes and applications within the spreadsheet. Some of the common applications of the LARGE function in Excel spreadsheets are given below:

• Finding the nth largest value with criteria
• To sum top n values
• To highlight top values
• Sorting the numbers ascending or descending
• Extracting multiple matches into separate columns
• To calculate top n values with criteria
• Getting nth match with INDEX/MATCH functions

While using the LARGE function, we can face a very common problem. This function can return the wrong value or return the #NUM! Error, even if the supplied value of n is between 1 and the number of values in the array provided in the formula. Possibly, this can arise when you have included text representations of the number within the supplied array. However, text values are ignored by the LARGE in Excel. It only recognizes numerical values. Therefore, we can solve this problem by converting all the array values into numeric ones if this problem arises.

If we get any error from the LARGE function in Excel, then it can be any one of the following-

#NUM! – This kind of error occurs when the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array. Apart from this, this error can also occur if the supplied array is empty.

#VALUE! - This error occurs when the supplied n is a non-numeric value.

• The LARGE in Excel is the function that is responsible for returning the nth largest value from a given set of values in the spreadsheet.
• The LARGE function is categorized as a Statistical Function.
• When sorted, the function returns a numeric value based on its position in a supplied list of values.
• If the nth_position is a larger value than the number of values in the array, then the LARGE in Excel will return #NUM! Error.
• If the supplied array is empty, then the LARGE function in Excel will be returning #NUM! Error.

This article is a guide to the LARGE Function in Excel. We discuss the LARGE formula Excel and how to use LARGE in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: -

• VBA IsEmpty
• Calculate P-Value in Excel
• Max IF in Excel
• Sort Data in Excel
• Highlight Every Other Row in Excel
• FIND Formula in Excel
• ISERROR in Excel
• INT Excel Function
• SLOPE Excel Function
• MAX in Excel
• MEDIAN in Excel
• FREQUENCY in Excel