Excel SMALL Function
The Excel SMALL function returns the numeric value from a specified range based on the nth smallest position
Example: Excel SMALL Function
|
=SMALL(B5:B9,1)
|
Result in cell E5 (15) - returns the smallest value from the specified range given the nth smallest value has been set as 1.
|
|
=SMALL(B5:B9,3)
|
Result in cell E6 (48) - returns the third smallest value from the specified range given the nth smallest value has been set as 3.
|
|
=SMALL(B5:B9,D5)
|
Result in cell E5 (15) - returns the smallest value from the specified range given the nth smallest value, as per the value in cell (D5) has been set as 1.
|
|
=SMALL(B5:B9,D6)
|
Result in cell E6 (48) - returns the third smallest value from the specified range given the nth smallest value, as per the value in cell (D6) has been set as 3.
|
METHOD 3. Excel SMALL function using the Excel built-in function library with hardcoded values
EXCEL
| =SMALL(B5:B9,1) Note: in this example we are searching for the smallest numeric value in the specified range. |
 |
METHOD 4. Excel SMALL function using the Excel built-in function library with links
EXCEL
| =SMALL(B5:B9,D5) Note: in this example we are searching for the smallest numeric value in the specified range, given the value in cell (D5) is 1. |
 |
Dim ws As Worksheet
ws.Range("E5") = Application.WorksheetFunction.Small(ws.Range("B5:B9"), 1)
ws.Range("E6") = Application.WorksheetFunction.Small(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 SMALL.
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 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 smallest: Select the nth smallest 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.Small(ws.Range("B5:B9"), ws.Range("D5"))
ws.Range("E6") = Application.WorksheetFunction.Small(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 SMALL.
Value Range: Have the range of value that you want to search from captured in range ("B5:B9").
nth smallest: Have the nth smallest 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 smallest: Select the nth smallest 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 SMALL function returns the numeric value from a specified range based on the nth smallest position.
=SMALL(array, n)
array: (Required) The range from which you want to return the nth smallest value.
n: (Required) A number value that specifies the smallest value to return from the array.
ADDITIONAL NOTES
Note 1: The SMALL 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 SMALL function will return an error (#NUM!).