SMALL
Formulas / SMALLIdentifies 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.
