SMALL
The SMALL function returns the Nth smallest value in a set of numbers.
This function can be useful when you need to determine which value is the smallest, second smallest, and so on.
Syntax
SMALL(set, N)
| Argument | Description | Permitted values |
|---|---|---|
| set | Array or range containing a set of numerical data | Range of cells or array of numbers |
| N | Position of the number (in ascending order) that needs to be returned | Positive integer (1, 2, 3...) or a reference to a cell containing a number |
Examples of use
Search for the smallest value
If you have data in cells A1:A5, for example, [10, 20, 5, 30, 15], then to find the smallest value, you can use:
=SMALL(A1:A5, 1)
This formula will return 5.
Search for the second smallest value in the same range
=SMALL(A1:A5, 2)
This formula will return 10.
Search for the third smallest value
=SMALL(A1:A5, 3)
This expression will return 15.
Notes
- If the N value is greater than the number of unique numbers in the array, the function will return the #N/A error.
- The function ignores text values and empty cells.