To make accurate average calculations without the influence of outliers, look no further than the TRIMMEAN function in Google Sheets. This powerful tool allows you to calculate the mean (average) of a dataset while excluding a user-specified proportion of data.
Why Choose TRIMMEAN Over AVERAGE?
Using the AVERAGE function in Google Sheets can lead to skewed results when outliers are present. These extreme values significantly impact the average calculation, often producing misleading averages. Let’s consider an example to demonstrate this issue.
Assume you have a dataset in Google Sheets that looks something like this:
If you use the AVERAGE function on this dataset, the outliers with values of 2200 and 12 will heavily influence the average calculation. These values lie far outside the range of most other values, distorting the average quantity sold. The calculated average using the AVERAGE function is 522.6.
=average(C2:C11)
However, by removing these outliers, you can obtain a more accurate average value, which in this case is 376.75.
In smaller datasets, you could manually exclude outliers as shown above. However, when dealing with larger datasets, this becomes impractical. This is where the TRIMMEAN function comes to the rescue.
TRIMMEAN – Syntax and Arguments
To use the TRIMMEAN function in Google Sheets, follow this syntax:
TRIMMEAN(data, exclude_proportion)
data: The range or array containing the dataset to consider.exclude_proportion: The proportion of the dataset to exclude from the top and tail.
The exclude_proportion value should be greater than or equal to 0 (or 0%) and less than 1 (or 100%). Bear this in mind when using the TRIMMEAN function.
For example, if you want to calculate the TRIMMEAN of the dataset in column C2:C11, excluding 20% of the extremes, you would use the following formula:
=TRIMMEAN(C2:C11,0.2) or =TRIMMEAN(C2:C11,20%)
The result of this formula would be 376.75, which excludes the outliers 2200 and 12 from the average calculation.
Conditional TRIMMEAN in Google Sheets
To perform conditional TRIMMEAN calculations in Google Sheets, you can utilize the FILTER or QUERY functions. These functions allow you to filter the dataset based on specific conditions.
Here’s an example of conditional TRIMMEAN using the FILTER function:
=trimmean(filter(C2:C11,B2:B11<>"Product 1"),10%)
In this formula, the TRIMMEAN calculation excludes the product “Product 1” from the dataset using the condition specified in column B.
Alternatively, you can achieve the same result using the QUERY function:
=trimmean(query(B2:C11,"Select C where B <> 'Product 1'"),10%)
By mastering these data manipulation functions (FILTER and QUERY), you can include more conditions in your average calculations while excluding outliers.
Conclusion
The TRIMMEAN function in Google Sheets provides a reliable way to calculate averages without the influence of outliers. By excluding a specified proportion of data, you can obtain accurate average values for your datasets. Take advantage of this powerful function and unlock the true potential of your data analysis.
For more tips and tricks on Google Sheets and other tools, visit Crawlan.com.
