-
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.
- SMALL will only look at numbers in the given range. If the range doesn’t contain numbers, you’ll 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'll receive an #INVALID DATA TYPE error.
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) |
Return 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) |
Return 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)) |
Return the value in the “Clothing Item” column that has the smallest number of “Units Sold”. |
Pants |