Return the k-th smallest value in a range
SMALL(array, k)
SMALL will select a value based on its relative positioning in a range of values, for example first, second or third place from the smallest value. It will evaluate cell references (A1,A2,A3) or ranges (A1:A3).
All numbers including negative values, percentages, dates, fractions, and times are assessed. Non-numeric values (e.g. text, TRUE/FALSE and empty cells) are ignored by the SMALL function.
If the arguments contain no numbers, SMALL returns the #NUM! error. Error values in the array argument cause errors.
SMALL examples.
In the examples below, the SMALL function is used to identify the 3rd and 5th smallest values from arrays of population and GDP per capita data and (using an array formula with the LEN function) the 7th snmallest number of characters in a selection of text strings.
Use the SMALL function to return the k-th smallest value in a range of values.
Syntax
=SMALL(array, k)
The SMALL function consists of 2 arguments:
- array. Required. The array or range of values from which to return the k-th smallest value in a range.
- k. Required. The position (e.g. 1st [k=1], 5th [k=5], 11th [k=11]) to return.
Try it now!
- Enter your formulas into the grey cells.
Scope
SMALL can be applied to cell references, ranges, named ranges, tables and arrays.
|
=SMALL((C3,C5,C7),2) |
|
=SMALL(C3:C7,2) |
|
=SMALL(myNamedRange,5) |
|
=SMALL(Table1[ColumnName],9) |
Caveats
The SMALL function:
- returns the #NUM! error if the array is empty.
- returns the #NUM! error if k is less than or equal to zero (<=0).
- returns the #NUM! error if k is greater than the number of data points.
- will assess all numbers including negative values, percentages, dates, fractions, and times.
- will ignore text values, empty cells and TRUE and FALSE values.
- will trigger an error if the arguments contain errors.
Related Functions
- MAX: return the largest value in a range of values.
- MIN: return the smallest value in a range of values.
- LARGE: returns the k-th largest value in a data set.
- RANK: return the rank of a number in a data set
Functions Category