LARGE Function (Table of Contents)
LARGE Function in Excel
When you want to find the second or third largest value from the given data set, this problem can be solved by using Excel LARGE function.
What is LARGE Function?
The LARGE function returns the nth largest value from a set of values. Means it returns the numeric values based on their position when sorted by value in descending order. It can retrieve “nth largest value” means the largest value, 2nd largest value, 3rd largest value etc.
The LARGE function comes under Statistical Functions category and it is a built-in function in excel. It is also known as the Worksheet function in excel. It can be put as part of a formula in a cell of a worksheet.
This function can be used to sort the information provided and find the maximum value.
LARGE Formula
Below is the LARGE Formula:
Where the passing arguments are:
- Array – It is the array or range of data from which we want to select the largest value.
- K – It is an integer value which specifies the position from the largest value.
As LARGE function falls under Statistical function category and it is a built-in function in excel, i.e. this function can be found under FORMULAS tab in excel. The steps are as follows:
- Click on FORMULAS tab.
- Click on More Functions tab.
- Select the Statistical functions category. It will open the drop-down list of functions.
- Click on LARGE functions tab from the drop-down list.
- After selecting the LARGE function, a Function arguments box will pop up.
- Enter the Array field with an array or range of data for which you want to find the nth largest value.
- Enter K field, it is the position from the largest value in the array of the value to return.
How to use LARGE Function in Excel?
LARGE Function is very simple to use. Let us now see how to use a LARGE function with the help of some examples.
Example #1
Suppose we have a company employee data and the company distributes the incentives to their employees based on their performance. We need to find out the name of the employees who are on top 3 in the list and received highest incentives.
For finding the employee name who has achieved the largest incentive, will use the below formula:
=LARGE(C4: C13,1)
The Result is:
For finding the employee name who has achieved the second largest incentive, will use the below formula:
=LARGE(C4: C13,2)
The Result is:
To find out the employee name who has achieved the 3rd largest incentive will use the below formula:
=LARGE(C4: C13,3)
The Result is:
The final results are:
Example #2
Let’s consider the below example with some values.
We need to arrange the above data in the order of Largest to smallest. With the help of LARGE function, we can do this very easily.
=LARGE(A25:A31,1)
The Result is:
Similarly, we find other values
Example #3
Let’s assume Sales data given and we want to see the total amount of sales from the top 5 performers.
We want to see the sales done by the top 5 sales employee:
We will apply the LARGE function for selecting the top 5 sales performer by passing the positions from 1 to 5 as an array as a second argument (k) position and sum those values.
The formula used for solving this problem is:
=SUM (LARGE(B37:B51,{1,2,3,4,5}))
The final result is:
Things to Remember about LARGE Function
- If the LARGE function returns the error value #NUM!, means
- The second argument value (k) is less than 1 or greater than the number of values in the given array.
- The given array is empty.
- If the function returns the error value #VALUE! – means the second argument (k) is non-numeric.
- The LARGE function is used to sort the data.
This has been a guide to Excel LARGE Function. Here we discuss the LARGE Formula and how to use LARGE function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
