Excel function · Statistics
SMALL
Returns the k-th smallest number, such as the second lowest.
What it does
SMALL is the mirror of LARGE: k = 1 is the minimum, k = 2 the second lowest. It suits price comparisons and fastest times.
Syntax
=SMALL(array, k)Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- array
- The numbers to rank.
- k
- Which place to return, counting from 1 for the smallest.
Example
Four quotes came in for the same job. What is the second-cheapest?
| A | B | C | |
|---|---|---|---|
| 1 | Quote | Second lowest | |
| 2 | 420 | 395 | |
| 3 | 380 | ||
| 4 | 455 | ||
| 5 | 395 |
The formula in C2:
=SMALL(A2:A5,2)Result: 395
Ordered from the bottom, the quotes are 380, 395, 420, 455. The second one is 395.
Common mistakes
- Expecting empty cells to count as 0. They are skipped, but typed zeros are not.
- Asking for a k larger than the number of values, which gives
#NUM!.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | SMALL | , |
| Deutsch | KKLEINSTE | ; |
| Français | PETITE.VALEUR | ; |
| Español (España) | K.ESIMO.MENOR | ; |
| Español (México) | K.ESIMO.MENOR | , |
| 日本語 | SMALL | , |
| 한국어 | SMALL | , |
| 繁體中文 | SMALL | , |
| ไทย | SMALL | , |
| ខ្មែរ | SMALL | , |
| Tiếng Việt | SMALL | ; |