Explanation
The SMALL function is meant to extract the "nth" smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value, supply 2, and so on.
=SMALL(data,1) // 1st smallest
=SMALL(data,2) // 2nd smallest
=SMALL(data,3) // 3rd smallest
In the example shown, "data" is the named range B5:B14. In this example, the main challenge is to increment a value for nth. This is done by using an expanding range inside the ROWS function:
ROWS($B$5:B5)
As the formula is copied down the table, the range expands, and the number of rows increases, which creates an incrementing number.
Sort numbers in descending order
To sort numbers in descending order, simply replace the SMALL function with the LARGE function:
=LARGE(data,ROWS(exp_rng))
Like SMALL, the LARGE function extracts an "nth" value. However, rather than the "nth smallest" LARGE returns the "nth largest".
Related formulas
Basic numeric sort formula
Basic text sort formula
Display sorted values with helper column
Random sort formula
Sort and extract unique values
Related functions
SMALL Function
The Excel 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.
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
Related videos
