ID EN
…
#Statistical

SMALL

Excel Functions

Get nth smallest value

Syntax

EXCEL 
=SMALL(array, k)

Arguments

Parameter Description
array An array or range of numeric values.
k Position as an integer, where 1 corresponds to the smallest value.

Return Value

The nth smallest value in the array.

Details

The SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data. The SMALL function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the smallest value in array, provide 1 for k. To return the fifth smallest value in array, provide 5 for k. To get nth largest values, see the LARGE function. In the formula below, the SMALL function returns the third smallest value in a list of five numbers provided in an array constant: Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd sma

Examples

Examples

In the formula below, the SMALL function returns the third smallest value in a list of five numbers provided in an array constant:

EXCEL 
=SMALL({29,14,33,19,17},3) // returns 19
Examples

Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd smallest values in a range:

EXCEL 
=SMALL(range,1) // 1st smallest value
=SMALL(range,2) // 2nd smallest value
=SMALL(range,3) // 3rd smallest value
Examples

In the example shown, the formulas in G5, G6, and G7 are, respectively:

EXCEL 
=SMALL(D5:D16,1) // returns 66
=SMALL(D5:D16,2) // returns 69
=SMALL(D5:D16,3) // returns 71

See Also

LARGE RANK MIN