Excel LARGE Function

This post will guide you how to use Excel LARGE function with syntax and examples in Microsoft excel.

Description

The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. It also can be used to get the nth largest value from a range of cells or an array.

For example, the LARGE function can find the first, second, third or nth smallest value in a list or an array.

The LARGE function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.

The LARGE function is available in Excel 2016, excel 2013, excel 2010, excel 2007, excel 2003, excel XP, excel 2000, excel 2011 for Mac.

Syntax

The syntax of the LARGE function is as below:

= LARGE (array,nth)

Where the LARGE function arguments are:

  • array -This is a required argument.  An Array or a range of cells that contains numeric values from which you want to get the nth largest value.
  • nth – This is a required argument.  The position of the number that you want to return

Note:

  • If the first argument is empty, then the LARGE function returns #NUM! Error.
  • If nth is less than or equal to 0 or if nth is greater than the number of values in array, the LARGE function returns the #NUM! Error.
  • If nth is the number of values in array, then LARGE (array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

Excel LARGE Function Examples

The below examples will show you how to use Excel LARGE Function to get the largest value form the numbers in a range.

#1 To get the largest value from a range B1:B4, just using the following excel formula:

=LARGE(B1:B4,1)

excel large function example1

The above formula returns the largest number from a range B1:B4, it returns 113.

 

x
How to Select Every Other Row in Excel

2# To get the second largest value from the Range B1:B4, you can use the following LARGE function:

=LARGE(B1:B4,2)

excel large function example2

The second argument nth in the above LARGE function is 2, so it will fetch the second largest function in the range B1:B4. It returns 34.

 

3# To get the largest value from the range B1:B4 that contains blank cells, enter into the following formula in the Cell C1:

=LARGE(B1:B4,1)

excel large function example3

The LARGE function will ignore blank cells in the Range. You will see that the Cell B3 is a blank cell, and it is ignored by the LARGE function. The largest value is still returned as 113.

4# To get the largest value from the range B1:B5 that contains text string, specific character and logical values. Using the following LARGE formula:

=LARGE(B1:B5,1)

excel large function example4

The LARGE function will ignore values in the range B1:B5 that contain text string, specific character and logical value.

5# To get the largest value from the range B1:B5 that contain an error, using the below formula:

=LARGE(B1:B5,1)

excel large function example5

More Excel LARGE Function Examples


  • Find the Largest Value Based on Multiple Criteria
    Assuming that you have a list of data that you want to find the largest value based on the product “excel” and the sales region “east”. You can create a new excel formula based on the SUMPRODUCT function and the LARGE function..……
  • Find Closest Value or Nearest Value in a Range
    You need to use an excel array formula based on the INDEX function, the MATCH function, the MIN function and the ABS function to find Closest Value or Nearest Value in a Range in Excel…
Related Posts

Average of Top N Values in Excel
Average of Top N Values 1

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average properly based on different situations. In this article, we will introduce you the way to calculate average of ...

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Extract Number from Text String in Excel
extract number from text string3

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using ...

Find the Earliest and Latest Date in a Range of Dates in Excel
find earliest date4

This post will guide you how to find the earliest date in a range of dates in Excel. How do I get the earliest and latest date in a range with a formula in Excel. How to find the highest ...

Find Closest Value or Nearest Value in a Range in Excel
find closest value3

This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. How do I find the closest match in a range to a given lookup value ...

Find the Largest Value Based on Multiple Criteria
find largest value4

This post will guide you how to find the largest value based on one criteria in excel. How do I return the largest value based on multiple criteria in excel. How to find and return the largest value based on ...

Excel SYD Function
excel syd examples1

This post will guide you how to use Excel SYD function with syntax and examples in Microsoft excel. Description The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric ...

Excel SLN Function
excel sln examples1

This post will guide you how to use Excel SLN function with syntax and examples in Microsoft excel. Description The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it ...

Excel PV Function
excel pv examples1

This post will guide you how to use Excel PV function with syntax and examples in Microsoft excel. Description The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest ...

Sidebar