Return lowest number
This tutorial shows how to return the lowest value from a range of values using Excel formulas, with the SMALL or MIN functions, or VBA
Example: Return lowest number
|
=SMALL(C5:C9,1)
|
This formula uses the Excel SMALL function, with number one (1) as the criteria, to return the lowest number from a selected range. In this example the formula will return the value captured in cell C6 (125) given it contains the smallest number in the selected range (C5:C9).
|
METHOD 2. Return lowest number using MIN function
EXCEL
|
=MIN(C5:C9)
|
This formula uses the Excel MIN function to return the lowest value from the selected range (C5:C9). In this example the formula will return the value captured in cell C6 (125) given it contains the lowest number in the selected range (C5:C9).
|
Sub Return_lowest_number()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return lowest number in a range
ws.Range("F4") = Application.WorksheetFunction.Small(ws.Range("C5:C9"), 1)
ws.Range("F4") = Application.WorksheetFunction.Small(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 lowest 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 lowest 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 lowest 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 lowest number. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Sub Return_lowest_number()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return lowest number in a range
ws.Range("F4") = Application.WorksheetFunction.Min(ws.Range("C5:C9"))
ws.Range("F4") = Application.WorksheetFunction.Min(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 lowest 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 lowest 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 lowest 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 lowest 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 lowest 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 lowest number from a range.
Both the Excel and VBA methods make use of the SMALL and MIN functions. With the SMALL function, you simply select the range from which you want to return the smallest number and assign number one (1) as the criteria, which will look for the lowest value in the selected range. Using the MIN function and selecting the range from which you want to return the lowest number it will find and return the lowest number from the range.
Both the Excel and VBA methods make use of the SMALL and MIN functions. With the SMALL function, you simply select the range from which you want to return the smallest number and assign number one (1) as the criteria, which will look for the lowest value in the selected range. Using the MIN function and selecting the range from which you want to return the lowest number it will find and return the lowest number from the range.
FORMULA (using SMALL function)
=SMALL(range, 1)
=SMALL(range, 1)
FORMULA (using MIN function)
=MIN(range)
=MIN(range)
ARGUMENTS
range: The range of cells from which you want to return the lowest number.
range: The range of cells from which you want to return the lowest number.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return highest number | How to return the highest 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 smallest value with criteria | How to return the nth smallest 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 |
|---|---|---|
| SMALL Function | The Excel SMALL function returns the numeric value from a specified range based on the nth smallest position | |
| MIN Function | The Excel MIN function returns the smallest value from a specified range of numeric values |