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.
Syntax
SMALL(array, k)
Inputs
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.. |
Example
Sample data
| A | B |
|---|---|
| Score | Student |
| 85 | Alice |
| 92 | Bob |
| 78 | Charlie |
| 95 | David |
| 88 | Eva |
Sample examples
| Formula | Description | Result |
|---|---|---|
=SMALL(A2:A6, 1) |
Returns the smallest score. | 78 |
=SMALL(A2:A6, 3) |
Returns the third smallest score. | 88 |
=SMALL(A2:A6, COUNT(A2:A6)) |
Returns the largest score (equivalent to MAX). | 95 |
=SMALL(A2:A6, ROUNDUP(COUNT(A2:A6)*0.2, 0)) |
Returns the score at the 20th percentile. | 78 |
=INDEX(B2:B6, MATCH(SMALL(A2:A6, 2), A2:A6, 0)) |
Returns the name of the student with the second lowest score. | Alice |
Notes
- 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, andSMALL(array,n)equals the largest value.
Tips
- 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.
- SMALL treats numbers stored as text as actual numbers, which may lead to unexpected results if your data isn't cleaned properly.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA