Excel LARGE Function (Example + Video)
When to use Excel LARGE Function
Excel LARGE function can be used to get the Kth largest value from a range of cells or array. For example, you can get the 3rd largest value from a range of cells.
What it Returns
It returns the value which is the Kth largest value from a range of cells.
Syntax
=LARGE(array, k)
Input Arguments
- array – the array or range of cells from which you want to fetch the kth largest value.
- k – the rank or position (from the top) of number that you want to fetch.
Additional Notes
- If array is empty, LARGE returns the #NUM! error value.
- If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.
- If n is the number of data points in a range, then LARGE(array,1) returns the largest value, and LARGE(array,n) returns the smallest value.
Examples – Excel LARGE Function
Here are six example of using Excel LARGE Function:
#1 Getting the largest value from a list
If you have a list of numbers (A2:A4) and you want to get the largest number from the list, you can use the following LARGE function: =LARGE(A2:A4,1)
Here the Kth value is 1, which would return the largest number from the range of numbers.
#2 Getting the second largest value from a list
To get the second largest value from the list (A2:A4), use the following formula: =LARGE(A2:A4,2)
Since the Kth value in the formula is 2, it returns the second largest values from the range of numbers.
#3 Using LARGE function when there are blank cells in the range
If there are any blank cells in the range, it is ignored by the LARGE function.
For example, in the above example, while we take the range of cells as A2:A5, the blank cell is ignored by the LARGE function while returning the largest value from this list.
#4 Using LARGE function when there are cells with text
Similar to blank cells, Excel LARGE function also ignores cells with text, alphanumeric characters, special characters, and logical values.
#5 Using LARGE function when there are cells with duplicates
If there are duplicates in the range used in the LARGE function, it will treat those duplicates as consecutive values. In the above example, two cells have the value 7. Now when you use LARGE function to return the largest and second largest value, it returns 7 in both the cases.
#6 Using LARGE function when there is Error in the cells
If there is an error in any of the cells used in the LARGE function, it will return an error.
Excel LARGE Function – Video Tutorial
Related Excel Functions:
- Excel INT Function.
- Excel MOD Function.
- Excel RAND Function.
- Excel RANDBETWEEN Function.
- Excel ROUND Function.
- Excel RANK Function.
- Excel MAX Function.
- Excel MIN Function.
- Excel SMALL Function.
Other articles you may also like:
