Excel LARGE Function
The Excel LARGE function returns the numeric value from a specified range based on the nth largest position
Example: Excel LARGE Function
|
=LARGE(B5:B9,1)
|
Result in cell E5 (105) - returns the largest value from the specified range given the nth largest value has been set as 1.
|
|
=LARGE(B5:B9,3)
|
Result in cell E6 (48) - returns the third largest value from the specified range given the nth largest value has been set as 3.
|
|
=LARGE(B5:B9,D5)
|
Result in cell E5 (105) - returns the largest value from the specified range given the nth largest value, as per the value in cell (D5) has been set as 1.
|
|
=LARGE(B5:B9,D6)
|
Result in cell E6 (48) - returns the third largest value from the specified range given the nth largest value, as per the value in cell (D6) has been set as 3.
|
METHOD 3. Excel LARGE function using the Excel built-in function library with hardcoded values
EXCEL
| =LARGE(B5:B9,1) Note: in this example we are searching for the largest numeric value in the specified range. |
 |
METHOD 4. Excel LARGE function using the Excel built-in function library with links
EXCEL
| =LARGE(B5:B9,D5) Note: in this example we are searching for the largest numeric value in the specified range, given the value in cell (D5) is 1. |
 |
Dim ws As Worksheet
ws.Range("E5") = Application.WorksheetFunction.Large(ws.Range("B5:B9"), 1)
ws.Range("E6") = Application.WorksheetFunction.Large(ws.Range("B5:B9"), 3)
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named LARGE.
Values Range: Have the range of values that you want to search from in range ("B5:B9").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cells references ("E5") and ("E6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Values Range: Select the range of values that you want to search from by changing range ("B5:B9") to any range in the worksheet, that doesn't conflict with the formula.
nth largest: Select the nth largest value that you want to search for by changing the values 1 and 3 directly in the VBA code.
Dim ws As Worksheet
ws.Range("E5") = Application.WorksheetFunction.Large(ws.Range("B5:B9"), ws.Range("D5"))
ws.Range("E6") = Application.WorksheetFunction.Large(ws.Range("B5:B9"), ws.Range("D6"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named LARGE.
Values Range: Have the range of values that you want to search from in range ("B5:B9").
nth largest: Have the nth largest values that you are searching in cells ("D5") and ("D6").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5") and ("E6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Values Range: Select the range of values that you want to search from by changing range ("B5:B9") to any range in the worksheet, that doesn't conflict with the formula.
nth largest: Select the nth largest value that you want to search for by changing the cell references ("D5") and ("D6") to any range in the worksheet, that doesn't conflict with the formula.
The Excel LARGE function returns the numeric value from a specified range based on the nth largest position.
=LARGE(array, n)
array: (Required) The range from which you want to return the nth largest value.
n: (Required) A number value that specifies the largest value to return from the array.
ADDITIONAL NOTES
Note 1: The LARGE function will only return numeric values.
Note 2: If the nth value is higher then the number of values in the specified range or is blank, the LARGE function will return an error (#NUM!).