SMALL

Formulas / SMALL
Identifies the nth smallest value in an array of given values
=SMALL(array,k)
  • array - an array of numeric values
  • k - an integer, representing position or rank

Examples

  • =SMALL({29,14,33,19,17},1)

    The SMALL function can be used to return the smallest value in a range. For example,the example returns 14 because that is the smallest item in the list.

  • =SMALL({29,14,33,19,17},2)

    The SMALL function can also be used to return the second or third smallest items in a range. For example,this returns 17 because it is the second smallest item in the list.

  • =SMALL(A1:A5,1)

    You can use the SMALL function to find the minimum value in a range by setting the second argument to 1. For example, the example will return the smallest value in the range A1:A5.

Summary

The SMALL function is used for extracting the kth smallest value from a given data set.

  • The SMALL function returns the smallest value at a specified position in a list, sorted by value in ascending order.
  • The value of the return value is the nth smallest value in the list, regardless of whether the values in the list are sorted or not.
  • SMALL ignores text values, empty cells, and TRUE or FALSE.
  • SMALL throws a #NUM! error if the array has no numbers.

Frequently Asked Questions

What is the SMALL function?
The SMALL function returns the kth least value in a data set.
What happens if the array argument is empty?
The SMALL function returns the #NUM! error if the array argument is empty.
What happens if k is less than 0 or k is bigger than the number of data points?
The SMALL function returns the #NUM! error if k is less than 0 or k is bigger than the number of data points.
How do I use the SMALL function to return the smallest value in a dataset?
The SMALL function returns the smallest value in a dataset by using SMALL(array,1).
How do I use the SMALL function to return the largest value in a dataset?
The SMALL function returns the largest value in a dataset by using SMALL(array,n) where n is the size of the data set.

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.