Returns the n-th lowest number in a provided range
Syntax
SMALL(range, n)
- range—The group of numbers to return the n-th lowest value from
- n—A number representing the position, from the lowest, to return
Sample usage
SMALL(Supply1:Supply5,3)
Usage notes
- SMALL only looks at numbers in the range you provide. If the range doesn't contain numbers, you receive an #INVALID VALUE or #INVALID COLUMN VALUE error.
- If the n value is higher than the total number of values in the range, you receive an #INVALID DATA TYPE error.
Examples
This example references the following sheet information:
| Row # | Clothing Item | Units Sold | Price Per Unit | In Stock? |
|---|---|---|---|---|
| 1 | T-Shirt | 78 | $15.00 | false |
| 2 | Pants | 42 | $35.50 | true |
| 3 | Jacket | 217 | $200.00 | true |
Given the table above, here are some examples of using SMALL in a sheet:
| Formula | Description | Result |
|---|---|---|
| =SMALL([Price Per Unit]:[Price Per Unit], 1) | Returns the 1st smallest number in the Price Per Unit column | $15.00 |
| =SMALL(COLLECT([Price Per Unit]:[Price Per Unit], [In Stock?]:[In Stock?], 1), 1) | Returns the 1st smallest number in the Price Per Unit column where the values in the In Stock column are 1 (checked/true). | $35.00 |
| =INDEX([Clothing Item]:[Clothing Item], MATCH(SMALL([Units Sold]:[Units Sold], 1), [Units Sold]:[Units Sold], 0)) | Returns the value in the Clothing Item column that has the smallest number of Units Sold | Pants |
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.