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)

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