How to Use TRIMMEAN Function in Excel

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

  1. Have your dataset ready in Excel. Ensure your data is in a single column or row for easy reference.
  2. 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%).
  3. 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)

See also  Case-Sensitive VLOOKUP

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).