SMALL

Description

Use this function to return the k-th smallest value in a data set. Supported in Chains. Can be used with CHILDREFS.

SMALL is useful for finding specific ranked values in a dataset, such as the third-smallest value or the bottom 10% of scores.

SMALL(array, k)

This function accepts the following arguments:

Name	Required	Description	Valid input
`array`	Yes	The array or range of data for which you want to determine the k-th smallest value.	A cell reference, a cell range, or a formula which results in either of these.
`k`	Yes	The position (from the smallest) of the value to return.	A positive integer from 1 to the number of items in the array, a reference to a cell containing a positive integer in that range, or a formula which results in either of these.

Use case	Formula	Explanation and Result
Find the lowest value in a data set.	`=SMALL(A2:A6, 1)`	Returns the smallest score among the five students. Result: 78
Find the value at a specific rank from the bottom of a data set.	`=SMALL(A2:A6, 3)`	Returns the third-lowest score among the five students. Result: 88
Find the highest value in a data set by asking for the last-ranked smallest value.	`=SMALL(A2:A6, COUNT(A2:A6))`	Counts the 5 scores, then returns the 5th-smallest — which is the same as the largest. Result: 95
Find the score that marks a specific percentile in a data set.	`=SMALL(A2:A6, ROUNDUP(COUNT(A2:A6)*0.2, 0))`	Calculates which rank corresponds to the 20th percentile of 5 scores (rounding up to rank 1), then returns the score at that rank. Result: 78
Find the name associated with a specific ranked value in a data set.	`=INDEX(B2:B6, MATCH(SMALL(A2:A6, 2), A2:A6, 0))`	Finds the second-lowest score (85), locates its position among the scores, then returns the matching student name at that position. Result: Alice

SMALL ignores empty cells and text values in the array.
SMALL is the opposite of LARGE.
SMALL can reference a maximum of 8,191 values.
If array is empty, SMALL returns the #NUM! error.
If k ≤ 0 or if k is greater than the number of data points, SMALL returns the #NUM! error.
When k = 1, SMALL returns the same result as MIN.
If n is the number of data points in array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.

Use SMALL in combination with INDEX and MATCH to find information associated with specific ranked values.
SMALL can be used to create "Top N" or "Bottom N" lists when combined with dynamic ranges.
For percentile calculations, combine SMALL with COUNT and ROUNDUP.
CAUTION: SMALL treats numbers stored as text as actual numbers, which may lead to unexpected results if your data isn't cleaned properly.