Documentation/Calc Functions/SMALL
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
SMALL
Category:
Statistical Analysis
Summary:
Retrieves the nth smallest value in a list of numbers. When used as an array function, it will retrieve an array of values in accordance with the required rank array representing their rank in the list.
Syntax:
SMALL(Data; RankC)
Returns:
Returns the RankC-th smallest value in the Data. It can also be an array of real numbers in the order of the required rank array.
Arguments:
Data is an array, or a reference to a range of cells, of data that contains real numbers. For logical values, TRUE is considered as 1, and FALSE as 0 (zero).
RankC is a positive integer value, or a reference to a single cell, or an array of values, or a reference to a range of cells containing them, which is/are the rank(s) of the numbers in Data. If RankC is an array, the function becomes an array function.
- If Data contains duplicate values, then the function ranks them incrementally. See examples below.
- If RankC is zero, negative, or larger than the size of Data, then the function returns an error.
- If RankC contains a non-numeric value, either being passed as an array or as a reference to the cells containing that value, then the function returns an error.
- If Data contains both non-numeric and numeric values, then the function considers the numeric values first and the non-numeric values last, returning the expected results for the numeric values and an error for the non-numeric data.
Additional details:
In each case an error is returned, the specific error (message) might vary, depending on the version of the software.
Examples:
Data1 | Data2 | RankC | |
---|---|---|---|
1 | 195 | 200 | 2 |
2 | 151 | 180 | 1 |
3 | 148 | 178 | 3 |
4 | 189 | 165 | 4 |
5 | 183 | 192 | 5 |
6 | 154 | 144 |
Formula | Description | Returns |
---|---|---|
=SMALL(A1:B6;2) | The second smallest value in A1:B6. | 148 |
=SMALL(A1:B6;C1:C6)
Using CSE. |
Array of the c-th smallest value in A1:B6 with ranks defined in C1:C6.
For the 6th value of rankC in C6 (blank cell), the function returns an error, keeping the rest of the result the same. |
148 |
144 | ||
151 | ||
154 | ||
165 | ||
Err:502 / Err:504 / #VALUE! | ||
=SMALL(A1:B6;{2;1;-3;4;5})
Using CSE. |
For a rankC value of -3, the function returns an error, keeping the rest of the result the same. | 148 |
144 | ||
Err:502 / Err:504 / #VALUE! | ||
154 | ||
165 |
Duplicate values in Data are ranked incrementally:
Data | Resulting Rank |
---|---|
1 | 1 |
2 | 2 |
3 | 3 |
3 | 4 |
4 | 5 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
SMALL