How to Use Excel LARGE Function (Examples + Video)

Excel LARGE Function (Example + Video)

Excel LARGE Function

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

Excel Large Function - Smallest 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

Excel LARGE Function - 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

Excel LARGE Function - Ignores Blanks

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

Excel LARGE Function - Ignores 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

Excel LARGE Function - 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

Excel LARGE Function - Error

 

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: