The TRIMMEAN function in Google Sheets is a powerful statistical tool that allows users to compute the mean of a dataset by excluding a certain percentage of both the highest and lowest values. This helps in obtaining a more accurate representation of the central tendency by eliminating outliers that could skew the results.
Syntax
TRIMMEAN(data, percent)
- data: The range of cells or array containing the numbers from which the mean will be calculated.
- percent: The percentage of data points to exclude from each end of the dataset, expressed as a decimal. For example, to exclude 10%, you would use 0.1.
Example #1
=TRIMMEAN(A1:A10, 0.2)
This function calculates the mean of the values in the range A1 to A10 while omitting the lowest 20% and the highest 20%. For instance, if A1:A10 contains the values {10, 20, 30, 40, 50, 60, 70, 80, 90, 100}, the TRIMMEAN would yield a result of 55.
Example #2
=TRIMMEAN(B1:B5, 0.4)
In this example, the function computes the mean of the values in B1 through B5, excluding 40% of both the extremes. If B1:B5 holds the numbers {2, 3, 4, 5, 100}, the resulting TRIMMEAN would be 3.67.
Example #3
=TRIMMEAN(C1:C12, 0.1)
Here, the TRIMMEAN function calculates the mean from the range C1 to C12, excluding 10% from each end of the dataset. For example, if C1:C12 contains {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}, the mean would be computed as 6.5, after excluding the lowest and highest values.
Error handling
- NUM!: This error occurs if the percent value is not between 0 and 1 or if there are not enough data points to trim the specified percentage.
- VALUE!: This error indicates that the data provided is not a valid range or array for the TRIMMEAN function to process.