SMALL Function

Returns the n-th lowest number in a provided range
Sample Usage
SMALL(Supply1:Supply5,3)
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
Usage Notes
  • 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.
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 Smartsheet Formula Template to view examples and practice working with functions in real-time. 


Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community