Table of Contents
In Google Sheets, you can calculate the average of a dataset excluding outliers by using the AVERAGEIF and COUNTIF functions, which allow you to specify a criterion for the data you want to include in the calculation. For example, if you want to calculate the average excluding values that are outside of a certain range, you can specify the range in the AVERAGEIF and COUNTIF functions, and the average will be calculated using only the data that meets the criteria.
There are two ways to calculate an average while excluding outliers in Google Sheets:
1. Use TRIMMEAN to Exclude Outliers
2. Use Interquartile Range to Exclude Outliers
We will use the following dataset in Google Sheets to illustrate how to use both methods:
Method 1: Use TRIMMEAN to Exclude Outliers
The TRIMMEAN function calculates the average of a range of values while excluding a certain percentage of observations from the top and bottom of the dataset.
For example, we can use the following formula to calculate the average value in column A while excluding a total of 20% of observations (10% from the top and 10% from the bottom):
=TRIMMEAN(A2:A16, 20%)
Since we have 15 values in our dataset 10% is 1.5, which is rounded down to 1.
Thus, this formula will calculate the average of the values in the range while excluding the smallest value and the largest value:
The average with outliers excluded turns out to be 58.30769.
Method 2: Use Interquartile Range to Exclude Outliers
The (IQR) is the difference between the 75th percentile (Q3) and the 25th percentile (Q1) in a dataset. It measures the spread of the middle 50% of values.
We can define an observation to be an outlier if it is 1.5 times the interquartile range greater than the third quartile (Q3) or 1.5 times the interquartile range less than the first quartile (Q1).
We can use the following formula to calculate the interquartile range for our dataset in Google Sheets:
=QUARTILE(A2:A16,3)-QUARTILE(A2:A16,1)
The following screenshot shows how to use this formula:
Next, we can use the following formula to use the IQR to identify outlier values and assign a “1” to any value that is an outlier in the dataset:
=IF(OR(A2<QUARTILE($A$2:$A$16,1)-1.5*$B$18,A2>QUARTILE($A$2:$A$16,3)+1.5*$B$18),1,0)
The following screenshot shows how to use this formula:
The only value that turns out to be an outlier is 164.
Lastly, we can use the following formula to calculate the average of all values in the dataset that are not outliers:
=AVERAGEIF(B2:B16, 0, A2:A16)
The following screenshot shows how to use this formula:
The average with outliers excluded turns out to be 55.42857.