Return highest number
This tutorial shows how to return the highest value from a range of values using Excel formulas, with the LARGE or MAX functions, or VBA
Example: Return highest number
|
=LARGE(C5:C9,1)
|
This formula uses the Excel LARGE function, with number one (1) as the criteria, to return the highest number from a selected range. In this example the formula will return the value captured in cell C7 (1,400) given it contains the largest number in the selected range (C5:C9).
|
METHOD 2. Return highest number using MAX function
EXCEL
|
=MAX(C5:C9)
|
This formula uses the Excel MAX function to return the highest value from the selected range (C5:C9). In this example the formula will return the value captured in cell C7 (1,400) given it contains the highest number in the selected range (C5:C9).
|
Sub Return_highest_number()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return highest number in a range
ws.Range("F4") = Application.WorksheetFunction.Large(ws.Range("C5:C9"), 1)
ws.Range("F4") = Application.WorksheetFunction.Large(ws.Range("C5:C9"), 1)
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to return the highest number by changing the range reference ("C5:C9") in the VBA code.
Worksheet Selection: Select the worksheet, by changing the Analysis worksheet name in the VBA code, which captures a range of cells from which you want to return the highest number. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to return the highest number by changing the range reference ("C5:C9") in the VBA code.
Worksheet Selection: Select the worksheet, by changing the Analysis worksheet name in the VBA code, which captures a range of cells from which you want to return the highest number. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Sub Return_highest_number()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return highest number in a range
ws.Range("F4") = Application.WorksheetFunction.Max(ws.Range("C5:C9"))
ws.Range("F4") = Application.WorksheetFunction.Max(ws.Range("C5:C9"))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to return the highest number by changing the range reference ("C5:C9") in the VBA code.
Worksheet Selection: Select the worksheet, by changing the Analysis worksheet name in the VBA code, which captures a range of cells from which you want to return the highest number. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to return the highest number by changing the range reference ("C5:C9") in the VBA code.
Worksheet Selection: Select the worksheet, by changing the Analysis worksheet name in the VBA code, which captures a range of cells from which you want to return the highest number. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to find and return the highest value from a specific range using Excel formulas or VBA.
This tutorial provides two Excel and VBA methods that can be used to return the highest number from a range.
Both the Excel and VBA methods make use of the LARGE and MAX functions. With the LARGE function, you simply select the range from which you want to return the largest number and assign number one (1) as the criteria, which will look for the largest value in the selected range. Using the MAX function and selecting the range from which you want to return the highest number it will find and return the largest number from the range.
Both the Excel and VBA methods make use of the LARGE and MAX functions. With the LARGE function, you simply select the range from which you want to return the largest number and assign number one (1) as the criteria, which will look for the largest value in the selected range. Using the MAX function and selecting the range from which you want to return the highest number it will find and return the largest number from the range.
FORMULA (using LARGE function)
=LARGE(range, 1)
=LARGE(range, 1)
FORMULA (using MAX function)
=MAX(range)
=MAX(range)
ARGUMENTS
range: The range of cells from which you want to return the highest number.
range: The range of cells from which you want to return the highest number.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return lowest number | How to return the lowest value from a range of values using Excel and VBA | |
| Lookup nth largest value | How to return the nth largest value in a range using Excel and VBA | |
| Lookup nth largest value with criteria | How to return the nth largest value in a range with criteria using Excel and VBA | |
| Lookup nth smallest value | How to return nth smallest value in a range using Excel and VBA | |
| Sum largest n numbers | How to sum the largest n numbers in a range using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| LARGE Function | The Excel LARGE function returns the numeric value from a specified range based on the nth largest position | |
| MAX Function | The Excel MAX function returns the largest value from a specified range of numeric values |