TRIMMEAN function. Average with extreme values (outliers/abnormal values) removed.(Microsoft Excel)

Japanese version.

The TRIMMEAN function is a function that removes outliers and outliers, which are the weak points of averages.

For example, if there are 4 cases of 300 and 1 case of 1,000, the result will be 440 when the average of the data is calculated.
In other words, the result is heavily influenced by outliers.

TRIMMEAN averages the data by truncating the upper and lower values by a certain threshold value.

How it works

ArrayRequiredThe number from which the average is calculated.
PercentRequiredSpecify the truncation percentage in decimals.
For example, 0.2 (20%) means that the top 10% and the bottom 10% are truncated.

Example Results

This is an example of using the TRIMMEAN and AVERAGE functions.

If 0.4 (40%) is specified for the percentage, the average is calculated by removing 20% (2 pieces) of the top and the bottom of the list, respectively. (Only the yellow background color of the data is used.)

Comparison with MEDIAN function

The median is also an indicator that corresponds to the weakness of the mean to outliers.

Depending on the application, it is often recommended to use the MEDIAN function rather than TRIMMEAN.

This is because the MEDIAN function is simpler and the median is more commonly used.

Also, there is no need to consider the truncation ratio (argument 2).


Spill when using the BYROW or BYCOL function.

Row direction
Column direction



Microsoft Excel Functions Statistical