Documentation/Calc Functions/TRIMMEAN

    From The Document Foundation Wiki

    Function name:

    TRIMMEAN

    Category:

    Statistical Analysis

    Summary:

    Calculates the arithmetic mean of a set of numbers, excluding a proportion of the smallest and largest values. This provides a simple method of ignoring outliers which may otherwise distort the calculation.

    Syntax:

    TRIMMEAN(Data; Alpha)

    Returns:

    Returns a real number, which is the arithmetic mean of the supplied data values excluding a specified proportion of the smallest and largest values.

    Arguments:

    Data is an unsorted array of real numbers, which is the data set for which the trimmed mean is to be calculated. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array.

    Alpha is a real number in the range 0 ≤ Alpha < 1 (expressed as a percentage or a decimal fraction), or a reference to a cell containing that number, which is the proportion of the original data set to be ignored in the mean calculation. The same number of values are ignored at each end of the sorted data set and since Alpha must be less than 1, it is not possible to ignore all numeric values in Data.

    • TRIMMEAN ignores any cell in Data that contains text or is empty.
    • If Data contains no numeric data, then TRIMMEAN reports a #VALUE! error.
    • If Alpha is non-numeric, then TRIMMEAN reports a #VALUE! error.
    • If Alpha is less than 0, or greater than or equal to 1, then TRIMMEAN reports an invalid argument error (Err:502).

    Additional details:

    • TRIMMEAN counts the number of numeric values ([math]\displaystyle{ N }[/math]) in Data and sorts them into ascending order.
    • The number of values to be excluded from the mean calculation at each end of the sorted list ([math]\displaystyle{ k }[/math]) is given by:
    [math]\displaystyle{ k\:=\: \text{INT}\left(\frac{N \times \alpha}{2}\right) }[/math]
    Where α is the value of the Alpha argument.
    • The mean of the remaining entries in the sorted list ([math]\displaystyle{ s }[/math]) is calculated as:
    [math]\displaystyle{ \text{TRIMMEAN}\;=\;\left(\frac{1}{N - 2k}\right)\displaystyle \sum_{i\,=\,k+1}^{N-k}s_i }[/math]
    • Note that [math]\displaystyle{ \text{TRIMMEAN} (Data, 0) = \text{AVERAGE}(Data) }[/math].
    • To understand how many numeric values are ignored for a data set comprising [math]\displaystyle{ N }[/math] values for a specified value of the Alpha argument ([math]\displaystyle{ \alpha }[/math]), utilize the following guidelines:
    1. No values are ignored if [math]\displaystyle{ 0 \le \alpha \lt \frac{2}{N} }[/math].
    2. One value is ignored at each end of the sorted list if [math]\displaystyle{ \frac{2}{N} \le \alpha \lt \frac{4}{N} }[/math].
    3. Two values are trimmed from each end of the sorted list if [math]\displaystyle{ \frac{4}{N} \le \alpha \lt \frac{6}{N} }[/math]
    4. ...and so on.

    Examples:

    Formula Description Returns
    =TRIMMEAN({80, 90, 100, 110, 50, 80, 110, 140, 205, 190, 362, 785}; 0.2) Here the function calculates the mean value of the 12 numbers in the given constant array, ignoring the lowest 10% and the highest 10% of values. With a value of 0.2 for the Alpha argument, then TRIMEAN excludes one value from each end of the sorted list for this data. Note that the formula =AVERAGE({80, 90, 100, 110, 80, 110, 140, 205, 190, 362 }) returns the same result. 146.7
    =TRIMMEAN({80, 90, 100, 110, "a", 50, 80, 110, 140, "b", 205, 190, 362, 785, "c"}; 0.2) Here the constant array contains the same numbers as that in the previous example, albeit with a few extra strings included. TRIMMEAN simply ignores the non-numeric data and returns the same result as the formula in the previous example. 146.7
    =TRIMMEAN(A1:A6; C1) where cells A1:A6 contain the values -4.5, -3, 4.5, 5, -1.5, and -4 respectively, while cell C1 contains 70%. Here the function sorts the supplied numbers into ascending order to give {-4.5, -4, -3, -1.5, 4.5, 5}. For a data set comprising six numbers, any value for the Alpha argument that is greater than ⅔ will cause the lowest two values and the highest two values to be ignored. In this case the mean is equivalent to the average of the middle two numbers in the sorted data set. -2.25

    Related LibreOffice functions:

    AVERAGE

    GEOMEAN

    HARMEAN

    ODF standard:

    Section 6.18.80, part 2

    Related (or similar) Excel functions:

    TRIMMEAN