tmean

Description

tmean returns the (optionally trimmed) mean as well as its standard error and parametric confidence interval. Trimmed means are robust location statistics and one can adjust the amount of trimming based on how long the tails of the parent population are expected to be.

Syntax

tmean(data, [gamma = 0], [alpha = 0.05]) where

data is the input data (any shape)
gamma is the optional amount of trimming from each tail. By default the untrimmed mean is returned.
alpha is the significance level of the confidence interval; by default a 95% confidence interval is returned (ie, significance level of 0.05 or 5%).

The function returns six values:

The (optionally trimmed) mean
The standard error of the (optionally trimmed) mean
The degrees of freedom (DOF) of the standard error
The margin of error (MOE), by default at 95% confidence. It is calculated using the critical t values, so it assumes that the sampling distribution of the mean is Gaussian
The lower boundary of the (parametric) CI
The upper boundary of the (parametric) CI

The standard error of the trimmed mean, $s(\overline{x}_\gamma)$ is calculated from the Winsorized standard deviation:

s(\overline{x}_\gamma) = \frac{s_w}{(1-2\gamma)\sqrt{n}}

where $s_w$ is the Winsorized standard deviation, $\gamma$ is the one-tailed trimming fraction, and $n$ is the number of values in the sample. For the untrimmed mean, $\gamma = 0$ and $s_w = s$ and the equation returns the familiar $s(\overline{x}) = \frac{s}{\sqrt{n}}$ standard error of the mean.

The margin of error (MOE) is the product of the critical t-value and the standard error, $t_\text{crit} s(\overline{x})$ . The bounds of the CI are calculated as $\overline{x} \pm t_\text{crit}s(\overline{x})$ .

Example

Below are several examples of using tmean to calculate the trimmed and untrimmed mean. Sometimes you only want a subset of the six values returned by the function; Excel’s TAKE and DROP functions are useful in those cases.

It is very important to note that tmean and Excel’s TRIMMEAN use different values when referring to the amount trimmed:

tmean takes $\gamma$ (the trimming fraction or percent) as a one-tailed fraction, meaning that this fraction of values (after sorting) are taken from both sides of the tail. Also note that it is a ceiling: for example, if there are 9 values in the sample, a 20% trimmed mean will result in dropping the highest and the lowest values from the sample because taking the two highest and two lowest would exceed 20%.
Excel’s TRIMMEAN uses $2\gamma$ as its argument: it is the TOTAL fraction of data points removed, half from right tail and half from the left.

Excel’s terminology is unusual, which is why tmean doesn’t use it. The majority of the statistical literature (and other statistical programs) uses the convention that a 20% trimmed mean refers to a single-tail trimming percentage (as a ceiling).

Download: tmean.xlsx

Source Code

tmean = LAMBDA(
    data, //input values (any shape)
    [gamma], //one-tail trimming fraction (default value is 0)
    [alpha], //desired significance level of CI/MOE (default is 0.05)
    IF(
        COUNT(data)<2, //need to account for n=0 or 1
        VSTACK(TRIMMEAN(data,0),"n/a",0,"n/a","n/a","n/a"),
        LET(
            gamma, IF(ISOMITTED(gamma) + NOT(ISNUMBER(gamma)), 0, gamma), // if omitted calculate untrimmed mean
            alpha, IF(ISOMITTED(alpha) + NOT(ISNUMBER(alpha)), 0.05, alpha), // if omitted calculates at 95% CL
            Xc, TOCOL(data),                // accept any shape
            X, FILTER(Xc,ISNUMBER(Xc)),     // but only numbers
            tmean, TRIMMEAN(X, 2 * gamma),
            n, COUNT(X),
            n_g, ROUNDDOWN(gamma * n, 0),
            stderr, IF(
                gamma = 0,
                STDEV.S(X) / SQRT(n), // CLT std error for untrimmed mean
                LET( // gamma > 0
                    // first create Winsorized sample
                    small_w, SMALL(X, n_g + 1), // smallest value retained
                    large_w, LARGE(X, n_g + 1), // largest value retained
                    data_w, IF(X < small_w, small_w, IF(X > large_w, large_w, X)),
                    // use to calculate std err of trimmed mean
                    STDEV.S(data_w) / ((1 - 2 * gamma) * SQRT(n))
                )
            ),
            DOF, n - 2 * n_g - 1,
            tcrit, T.INV.2T(alpha, DOF),
            MOE, tcrit * stderr,
            LL, tmean - MOE,
            UL, tmean + MOE,
            VSTACK(tmean, stderr, DOF, MOE, LL, UL)
        )
    )
);

KDEmax MedianCI