TRIMMEAN function

The TRIMMEAN function in Excel is used to calculate the mean (average) of a data set while excluding a percentage of the data points at the top and bottom. This function is particularly useful when you want to remove extreme values (outliers) that could skew the average and get a more accurate representation of the central tendency of the data.

The function trims the specified percentage of data points from both ends of the data set before calculating the mean.

Syntax

=TRIMMEAN(data, percent)

Parameters

1. data (required): The range or array of data points for which you want to calculate the trimmed mean.
2. percent (required): The percentage of data points to exclude from both the top and bottom of the data set. This value should be between 0 and 100, and it represents the total percentage of data to exclude. For example, if you set the percent to 20, Excel will remove 10% of the data from the top and 10% from the bottom.

Key Points

• Excluding Data: The TRIMMEAN function removes a certain percentage of the smallest and largest data points before calculating the average, helping to minimize the impact of outliers.
• Even and Odd Data Sets: If the data set has an odd number of data points, TRIMMEAN will exclude a rounded number of points from the top and bottom. For even data sets, the function will exclude an even number of points.
• Percent: If you choose a higher percentage for the percent argument, more data points will be excluded from both ends, resulting in a mean that is even less influenced by extreme values.

Example Usage

1. Trim 10% of Data

Suppose you have the following data representing test scores:

You want to calculate the trimmed mean by excluding 10% of the data from both the top and bottom.

• data: A2:A10 (Test Scores)
• percent: 10 (This means 10% of the data will be trimmed from both the top and bottom)

To calculate the trimmed mean:

=TRIMMEAN(A2:A10, 10)

Result: The formula will return the trimmed mean after removing the top and bottom 10% of the values (in this case, the smallest and largest test scores).

2. Trim 20% of Data

If you wanted to trim 20% of the data (i.e., 10% from the top and 10% from the bottom), you would use the following formula:

=TRIMMEAN(A2:A10, 20)

Result: The formula will exclude 20% of the data (top 10% and bottom 10%) and return the mean of the remaining data points.

Notes

• Handling of Data: If the number of data points is small, the percentage of data to be excluded will affect the trimmed mean significantly. If there are fewer than 3 data points, the function will return an error, as you can’t exclude data from both ends with such a small dataset.
• Outlier Reduction: The TRIMMEAN function is often used when data contains outliers that could distort the true average. For example, in financial or scientific data, where extreme values may not be typical of the overall population, trimming helps produce a more reliable mean.
• Percent Values: If you want to exclude 5% of the data from both ends, use percent = 5. A percent = 0 means no data is excluded, and it’s just a standard mean calculation.

Related Functions

• AVERAGE: Calculates the mean of a set of data, without excluding any values.
• MEDIAN: Finds the middle value in a dataset, which can also help reduce the impact of outliers.
• TRIM: Removes extra spaces from text, but does not affect numerical values.
• PERCENTILE.EXC / PERCENTILE.INC: Returns the k-th percentile of a dataset, which can be used in conjunction with TRIMMEAN for percentile-based trimming.
• AVERAGEIF / AVERAGEIFS: Calculates the average of data points that meet specified criteria, potentially excluding certain values based on conditions.

The TRIMMEAN function is a useful tool for calculating the mean of data while reducing the influence of outliers or extreme values, making it especially valuable in situations where you want a more robust measure of central tendency.