Documentation/Calc Functions/SMALL

    From The Document Foundation Wiki

    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:

    LARGE

    RANK

    ROUNDDOWN

    ODF standard:

    Section 6.18.70, part 2

    Related (or similar) Excel functions:

    SMALL