Syntax. TRIMMEAN(array,percent)
Definition. This function returns the average value of a data group, excluding the values from the top and bottom of the data set. TRIMMEAN() calculates the average of a subset of data points excluding the smallest and largest values of the original data points based on the percentage. Use this function to exclude outlying data from your analysis.
Arguments
array (required). The array or range of values to trim and average.
percent (required). The percentage of data points to exclude from the calculation. For example, if percent = 0.2, four points are trimmed from a data set of 20 points (20 x 0.2): two from the top and two from the bottom of the set.
Note
If percent is less than 0 or greater than 1, the TRIMMEAN() function returns the #NUM! error.
TRIMMEAN() rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1 (10 percent), three of 30 data points are excluded. For symmetry, TRIMMEAN() excludes a single value from the top and bottom of the data set.
Background. Usually all values in a data set are used to calculate the mean. However, you might want to exclude the marginal areas to calculate a mean without outliers to get a trimmed mean.
If the data contains outliers—that is, a few values that are too high or too low—sort the observed values in ascending order, trim the values at the beginning and at the end, and calculate the mean from the remaining values.
To get a mean trimmed by 10 percent, omit 5 percent of the values at the beginning and 5 percent at the end.
Example. You are the executive manager of the controlling department of a software company and have compiled the sales for the past 17 months, from January 2007 to May 2008. You want to calculate the average sales and exclude outliers because you want to sort the sales in ascending order.
If you use the AVERAGE() function, the result is not meaningful because the outliers are included in the calculation and impact the result. The MEDIAN() function also doesn’t return the correct result. Therefore, you use the TRIMMEAN() function.
As you can see in Figure 12-149, the calculation of the average sales returns a higher value because of the outliers.
You specify 0.12 for the percent argument. This means that 6% from the higher and 6% from the lower values are excluded. The trimmed mean returns an average sale of $154,624.40.