The TRIMMEAN function in Excel calculates the mean (average) of a dataset after excluding a specified percentage of data points from the top and bottom of the dataset. This function is particularly useful for reducing the impact of outliers on the mean calculation, providing a more representative average of the central tendency of a data set.
Syntax
The TRIMMEAN function syntax is:
TRIMMEAN(array, percent)
- array: The range or array of values for which you want to calculate the trimmed mean.
- percent: The percentage of data points to exclude from the calculation. This value should be between 0 and 1. For example, if you specify 0.1, Excel excludes the top 5% and the bottom 5% of data points (10% in total).
How to Use TRIMMEAN Function
- Have your dataset ready in Excel. Ensure your data is in a single column or row for easy reference.
- Decide on the percentage of data points you wish to exclude from both ends of your dataset. Remember, the value must be between 0 (0%) and 1 (100%).
- Enter the TRIMMEAN function, referencing your dataset as the array and your chosen percentage as the percent. For example, =TRIMMEAN(A1:A100, 0.1) calculates the trimmed mean of the values in cells A1 through A100, excluding the top 10% and bottom 10% of data points.
Example
Suppose you have a dataset in cells A1 through A20 and you want to calculate the trimmed mean, excluding the top and bottom 10% of data points. Your formula in a chosen cell would look like this: =TRIMMEAN(A1:A20, 0.1)
This formula calculates the mean of the given range after removing the highest 10% and the lowest 10% of the values (in this case, two values from each end if you have 20 data points, since 10% of 20 is 2).
TRIMMEAN rounds down the number of excluded data points. For instance, if excluding 10% of 50 data points, it removes the top 2.5% and the bottom 2.5% of data points, which results in excluding 2 points from each end (since it rounds down the 2.5 data points to 2).
