Syntax
SMALL(array, k)
| Parameter | Description |
|---|---|
| array | Parameter of the SMALL function. |
| k | Parameter of the SMALL function. |
Examples
Cheapest supplier quote
Formula
=SMALL(C2:C20, 1)
Returns the lowest quote from 19 suppliers. Equivalent to MIN(C2:C20) when k=1.
3rd fastest delivery
Formula
=SMALL(E2:E100, 3)
Returns the 3rd shortest delivery time in days. Useful for finding a realistic fast-delivery benchmark excluding the top 2 outliers.
Bottom 5 scores
Formula
=SMALL(B2:B500, ROW(A1:A5))
Returns the 5 lowest scores when entered as an array formula, useful for identifying students or employees who need the most help.
Common Errors
#NUM!
Returned if k is larger than the number of data points or less than 1. For 10 values, k must be between 1 and 10.
#VALUE!
Occurs when k is non-numeric.
Tips
SMALL(array, 1) equals MIN
For the absolute minimum, MIN and SMALL with k=1 are identical. SMALL is useful when you want the 2nd, 3rd, or n-th smallest.
Exclude zeros
To find the smallest non-zero value, use an array formula like =SMALL(IF(A1:A100>0, A1:A100), 1) to filter out zeros first.
Pair with LARGE for trimmed ranges
Use SMALL and LARGE together to exclude outliers: average everything between SMALL(data,3) and LARGE(data,3) to trim the top and bottom 2 values.
Try SMALL in Viztab
Import your data and use SMALL with 370+ other formulas. No signup required.
Open Viztab