LARGE in Excel (Table of Contents)
LARGE Function in Excel
The Microsoft Excel LARGE Function is the function which 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 and is categorized as a Statistical Excel Function. This function can be entered as a part of the formula in a cell of Excel worksheet. The LARGE in Excel basically returns a numeric value which is totally 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 value, 2nd largest value, 3rd largest value etc.
LARGE Formula in Excel
Parameters of the LARGE Function in Excel
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 is an integer which specifies the position from the value which is the largest, i.e. the nth position.
How to Use the LARGE in Excel?
1. You can simply enter the desired LARGE function formula in the required cell to attain a return value on the argument.
2. You can manually open the LARGE formula in Excel dialogue box in the spreadsheet and enter the logical values to attain a return value.
3. Consider the screenshot below to see the LARGE Function in Excel option under the Statistical Function menu.
4. Click on the LARGE option. The LARGE formula in Excel dialogue box will open where you can put the argument values to obtain a return value.
The return value will be a numeric value which is 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, then the LARGE Excel function will return #NUM! error. If the supplied array is empty, then the LARGE function will be returning #NUM! error.
- The LARGE Function in Excel is a useful function when you are going to retrieve the nth highest value from the supplied set of data.
- For example, the LARGE Function can be used to find the first, second or the third highest scores of a test.
- Similar to the SMALL Function in Excel, the LARGE Excel Function also retrieves numeric values which are based on their position in a supplied list when they are 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 its working.
How to Use LARGE Function in Excel with Examples
Let’s look below at some of the examples of LARGE function in Excel. These examples will help you in exploring the use of LARGE in Excel.
Based on the above Excel spreadsheet, let’s consider these examples and see the LARGE function return based on the syntax of the function.
Consider the below screenshots of the above examples for clear understanding.
LARGE in Excel Example #1
Apply the LARGE formula in Excel =LARGE(A1:A5, 1), we get 48
LARGE in Excel Example #2
Using the LARGE Formula in Excel =LARGE(A1:A5, 2), to get 9
LARGE in Excel Example #3
Apply the LARGE formula here =LARGE(A1:A5, 3), to get 6.9
LARGE in Excel Example #4
Now apply the LARGE in Excel here =LARGE(A1:A5, 4) to get 5
LARGE in Excel Example #5
Here we apply the LARGE formula in Excel =LARGE(A1:A5, 5) to get -3.7
LARGE in Excel Example #6
Here we use the formula to calculate the LARGE in excel =LARGE((6, 23, 5, 2.3), 2)
Some Applications of LARGE Function in Excel
The Microsoft Excel LARGE Function can be used for various purposes and application within the spreadsheet. Some of the common applications of 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 sum top n values with criteria
- Getting nth match with INDEX/MATCH Functions
LARGE Function in Excel – Common Problem
While using the LARGE Function, you can face a very common problem, i.e. 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, which is supplied in the formula. Possibly, this can arise when you have included text representations of the number within the supplied array. Text values are ignored by the LARGE in Excel and it only recognizes numerical values. Therefore, if this kind of problem arises, then you can solve it by converting all the array values into numeric values.
LARGE Function Errors
If you get any kind of 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 kind of error occurs when the supplied n is non-numeric value.
Things to Know about the LARGE Function in Excel
- The LARGE in Excel is the function which 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.
- The function basically returns a numeric value which is totally based on their position in a supplied list of values when sorted.
- 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 has been a guide to LARGE Function in Excel. Here we discuss the LARGE Formula excel and how to use LARGE in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- VBA IsEmpty Function in Excel
- How to Calculate P-Value in Excel?
- Max IF in Excel
- Where to Find Sort Option 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