TRIMMEAN

What it does

TRIMMEAN(array, percent)

TRIMMEAN(array, percent) returns the trimmed mean — the arithmetic mean of the data after discarding an equal number of the largest and smallest values.

The trimming rule is the part that needs stating precisely, because it is not "discard percent of the data". Microsoft documents it in two sentences: percent is "the fractional number of data points to exclude from the calculation", and "TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2". So with n numeric values:

total excluded  =  2 · floor( n · percent / 2 )
excluded per end,  k  =  floor( n · percent / 2 )
result = mean of the n − 2k values remaining after sorting

The documented example fixes the reading: with percent = 0.2 and 20 points, four points are trimmed — two from the top and two from the bottom.

The rounding-down-to-an-even-count rule exists to keep the trim symmetric. An odd excluded count would have to remove one more value from one end than the other, which would bias the estimator; Excel would rather trim slightly less than trim asymmetrically. This is the right choice, and it means the effective trimming fraction is generally below the requested percent — a fact worth knowing before comparing TRIMMEAN results against a statistics package that rounds differently. There is no universal convention here: implementations variously round the per-end count down, round it to nearest, or interpolate between order statistics, and they disagree on the same data by construction rather than by accident.

As an estimator, TRIMMEAN interpolates between two familiar ones:

Its breakdown point is the fraction trimmed from each end, k/n ≈ percent/2: that is the proportion of arbitrarily corrupted observations the estimator tolerates before its value can be driven anywhere. The mean has breakdown point 0; the median has ½. TRIMMEAN lets the caller choose a point on that line, which is the whole reason robust location estimators exist (Tukey; Huber, Robust Statistics; Hampel, Ronchetti, Rousseeuw & Stahel).

Note also what it is not: it is not a Winsorized mean, which replaces the extreme values with the nearest retained value rather than discarding them, and which therefore has a different variance and a different influence function. Excel offers no Winsorized mean.

Arguments

Try it

Quirks

Result and edge cases

Returns Number.

• percent = 0 trims nothing and equals AVERAGE(array). This should be an exact identity between two Excel surfaces, including in the last bits if both sum the same way — and it is therefore a good probe of whether TRIMMEAN reuses AVERAGE's accumulation.
• percent small enough that n · percent < 2 also trims nothing, because the floor of a quantity below 2, halved, is zero. So on a ten-point data set every percent below 0.2 behaves identically to percent = 0. That is a large flat region that surprises people who expect percent = 0.1 to do something.
• Ties at the trimming boundary. Trimming is by position in the sorted order, not by value, so when several observations tie at the cut point it does not matter which copies are removed — the retained multiset, and therefore the mean, is the same. This is a real robustness of the definition and it is worth knowing that no tie-breaking rule is needed.
• n = 1 with any admissible percent retains the single value.
• Sort order and non-finite values. The definition depends on an ordering of the data. What happens with values that do not order conventionally, and how blanks and text interact with the sort, is undocumented.
• array empty is undocumented; the engine's battery reports a value error for an empty range.

Errors

As documented on Microsoft's TRIMMEAN page:

That is the whole documented table. Undocumented, and therefore open: an empty array; the percent = 1 even-n case that leaves no data; non-numeric contents of array; and a nonnumeric percent.

Errors in either argument propagate under the universal coercion rule. The Handbook has not verified any of this against Excel.

How to implement

Numerical notes

TRIMMEAN is the rare statistical surface whose difficulty is combinatorial rather than floating-point. The value is a mean of a subset, so once the subset is chosen the arithmetic is ordinary; almost everything that can go wrong goes wrong in choosing it.

Selection, not sorting. A full sort is O(n log n) and is the obvious implementation, but the function only needs the k-th smallest and k-th largest cut points; a two-sided quickselect (Hoare's algorithm, or nth_element) does the job in expected linear time, and introselect guarantees it. For the array-lifted case — many percent values against one array — sorting once and reusing the order is obviously right, and an implementation that re-selects per element does needless work.

The count arithmetic is integer arithmetic and should stay that way. floor(n · percent / 2) computed in floating point can land on the wrong integer when n · percent is within rounding of an even number: n = 20, percent = 0.2 gives n·percent = 4.000000000000001 or 3.9999999999999996 depending on how the product is formed, and the second rounds down to a different trim count. This is a real, reachable discontinuity — one ulp in the product changes how many observations are discarded, and therefore changes the answer by a visible amount rather than by a rounding error. Any implementation of this function needs a defensible rule for forming the count, and the Handbook does not know what Excel's is.

Summation of the retained values. The ordinary accumulation question applies: naive left-to-right summation of a long retained set accumulates error proportional to n; pairwise or compensated (Kahan/Neumaier) summation reduces it. One pleasant side effect of trimming is that the retained values span a narrower range than the original data, so the sum is generally better conditioned than AVERAGE's on the same input.

Sorting does not lose accuracy, but it does fix an order, and summing in sorted order is a different floating-point sum from summing in sheet order. So TRIMMEAN(array, 0) and AVERAGE(array) are mathematically identical and need not be identical in the last bit — which makes their comparison a probe rather than a tautology.

The accuracy and convention behaviour of Excel's statistical procedures is a documented topic in the published literature; Morten Welinder's work on Gnumeric's statistical functions is the most concrete account, and the assessments by Knüsel and by McCullough & Wilson are the standing evaluations. The Handbook names them as the right reading and does not assert from them what any current build does.

Rust code

49 trimmean "TRIMMEAN" (2, 2) [R "array", V "percent"] |s, a, sv| {
    let mut values = list(s, a, 0, sv)?;
    let percent = num(s, a, 1)?;
    out(s, k::moment_stats_family::trimmean_kernel(&mut values, percent))
}

pub(crate) fn trimmean_kernel(values: &mut [f64], percent: f64) -> Result<f64, WorksheetErrorCode> {
    if !percent.is_finite() {
        return Err(WorksheetErrorCode::Value);
    }
    // A percent of 1 is #NUM! too, though Microsoft's page names only one above 1 (Excel
    // 16.0.20430, 2026-10-25, dc-6au: `=TRIMMEAN(A1:A3,1)` #NUM!, `=TRIMMEAN(A1:A3,0.9999999999)` 2).
    if !(0.0..1.0).contains(&percent) {
        return Err(WorksheetErrorCode::Num);
    }
    if values.is_empty() {
        return Err(WorksheetErrorCode::Num);
    }
    let mut sorted = values.to_vec();
    sorted.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
    let to_trim = (percent * values.len() as f64).floor() as usize;
    let each_side = to_trim / 2;
    if each_side * 2 >= values.len() {
        return Err(WorksheetErrorCode::Num);
    }
    // Excel adds the kept values in their own order, not the sorted order (dc-j9k: 20 of 20
    // TRIMMEAN readings in the catalogue oracle's sheet dcj9k_statistics; the sorted sum
    // matched 18): each of the `each_side` smallest and largest values is left out once.
    let mut trimmed: Vec<f64> = sorted[..each_side]
        .iter()
        .chain(sorted[sorted.len() - each_side..].iter())
        .copied()
        .collect();
    let mut total = 0.0;
    for value in values.iter() {
        if let Some(at) = trimmed.iter().position(|t| t == value) {
            trimmed.swap_remove(at);
        } else {
            total += value;
        }
    }
    Ok(total / (values.len() - 2 * each_side) as f64)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• AVERAGE is the percent = 0 case and the estimator TRIMMEAN is a robustification of.
• MEDIAN is the limit as the trimming approaches everything, for odd n. TRIMMEAN with a large percent and MEDIAN should agree exactly on odd-length data whenever the trim leaves a single value.
• AVERAGEIF / AVERAGEIFS trim by predicate rather than by rank; they are the other way to exclude outliers and they are not robust in the breakdown-point sense, because a predicate chosen from the data is itself corruptible.
• QUARTILE.EXC / PERCENTILE.INC / SMALL / LARGE are the other order-statistic surfaces, and the interpolation conventions they disagree about are the same family of convention disagreements that make TRIMMEAN non-portable across statistics packages.
• STEYX, SKEW, SKEW.P, KURT share this surface's implementing module in the reference engine — a module of moment statistics with one order statistic sitting inside it.
• Confused with: a Winsorized mean (see above), and with "remove outliers then average", which is a data-dependent rule with no fixed breakdown point.

Named variants

No named variants are recorded yet.