Documentation/Calc Functions/TRIMMEAN

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

TRIMMEAN

Category:

Statistical Analysis

Summary:

Calculates the mean of a data set ignoring a percentage of the values at the margins.

Syntax:

TRIMMEAN(Data; Alpha)

Returns:

Returns a real number which is the mean of a data set ignoring a specified percentage of data at the margins.

Arguments:

Data is an array or a reference to a range of cells of data in the sample.

Alpha is a real number between 0(inclusive) and 1(exclusive) which is the percentage of the marginal data that will not be taken into consideration. It is such that the same number of points are dropped at each end.

  • If Data contains a text value either passed in double-quotes in an array or a reference to the cell containing that text then the function ignores that value. However, while passing in an array if the text is not enclosed in double-quotes then the function returns an error value (Err:539).
  • If Alpha is less than 0 or greater than or equal to 1 then the function returns an error value.

Additional details:

  • The formula for TRIMMEAN is:

We sort the values in Data before using them. So

Cutoff.png

and TRIMEAN =

Trimmean.png

where sortedDatai is the ith value after sorting the Data in ascending order, n is the number of data points in Data and Alpha is Alpha.

  • It is TRIMMEAN(data, 0) = AVERAGE(data).

Examples:

Formula Description Returns
=TRIMMEAN({80,90,100,110,50,80,110,140,205,190,362,785},0.2) The function calculates the mean value of numbers in given data, without taking into consideration the lowest 10% of values and the highest 10% of values. The percentage numbers refer to the amount of the untrimmed mean value, not to the number of summands. 146.7
=TRIMMEAN({80,90,100,"a",50,80,110,140,205,190,362,785},0.2) The function calculates the mean value of numbers in given data, without taking into consideration the lowest 10% of values and the highest 10% of values. The percentage numbers refer to the amount of the untrimmed mean value, not to the number of summands. Here the text value "a" is ignored in calculation. 150.777777777778
=TRIMMEAN({80,90,100,a,50,80,110,140,205,190,362,785},0.2) The function returns an error value since a text value is passed in array for Data without double quotes. Err:539.
=TRIMMEAN({80,90,100,110,50,80,110,140,205,190,362,785},0.5) The function calculates the mean value of numbers in given data, without taking into consideration the lowest 25% of values and the highest 25% of values. The percentage numbers refer to the amount of the untrimmed mean value, not to the number of summands. 123.333333333333

Related LibreOffice functions:

AVERAGE

GEOMEAN

HARMEAN

ODF standard:

Section 6.18.80, part 2

Equivalent Excel functions:

TRIMMEAN