How to calculate average excluding outliers in google sheets?

Why Exclude Outliers?

An outlier is an observation point that is distant from other observations. While sometimes these points represent legitimate data that should be included, in many cases, they signify errors in data collection, measurement malfunctions, or highly unusual, non-representative events. When calculating simple descriptive statistics like the mean, even a single extreme value can disproportionately pull the average toward itself, compromising the integrity of the analysis.

Excluding these values ensures that the calculated average provides a better measure of the central tendency for the majority of the data. This practice is particularly critical in fields such as financial modeling, quality control, and psychological research, where slight biases in the mean can lead to significant decision-making errors. By applying outlier exclusion techniques in Google Sheets, analysts can achieve a more reliable and stable statistic that truly reflects the typical value within the dataset.

Overview of Outlier Exclusion Methods in Google Sheets

Google Sheets offers flexibility when dealing with extreme data points. The choice between methods often depends on the level of statistical rigor required and the desired speed of implementation. We will explore two primary strategies for defining and excluding non-representative values from our average calculation.

For the purpose of illustrating these methods, we will use the following sample dataset shown below. This dataset represents 15 observations (A2:A16) and will be the common reference for both the TRIMMEAN and the IQR approach.

There are two primary ways to calculate an average while effectively excluding outliers in Google Sheets:

1. Use TRIMMEAN to Exclude a Fixed Percentage of Data.
2. Use the Interquartile Range (IQR) to Define and Exclude Statistical Outliers.

We will use the following dataset in Google Sheets to illustrate how to apply both methods:

Method 1: Utilizing the TRIMMEAN Function

The TRIMMEAN function offers the quickest and simplest way to calculate a mean after excluding a specified percentage of data points from both ends of the distribution. This function automatically sorts the data, identifies the smallest and largest values corresponding to the exclusion percentage, and then calculates the average of the remaining data.

The syntax for the function is TRIMMEAN(data, percent_to_exclude), where the data parameter specifies the range of values, and percent_to_exclude represents the proportion of data to be removed from the calculation (split evenly between the top and bottom tails). This approach is highly useful when you suspect measurement error or random noise contributes to extreme values, but you do not need to rely on complex statistical definitions like the Interquartile Range (IQR).

Step-by-Step TRIMMEAN Calculation

To demonstrate, let’s calculate the average value in column A while excluding a total of 20% of the observations—meaning 10% will be trimmed from the smallest values and 10% from the largest values. This process effectively removes the most extreme observations, resulting in a trimmed mean.

We apply the function as follows, referencing our data range A2:A16 and the exclusion percentage 20%:

=TRIMMEAN(A2:A16, 20%)

Since our dataset contains 15 values, calculating 20% of 15 yields 3 total observations to be excluded (1.5 observations from the top and 1.5 from the bottom). Since the trimming must occur in whole numbers, Google Sheets rounds the count down, resulting in the exclusion of the single smallest value and the single largest value (2 total excluded points, or 10% from each side). This leaves 13 values contributing to the final average.

The resultant calculation, shown below, confirms the removal of the extremes and the calculation of the average based on the remaining core dataset:

After excluding the calculated extremities, the trimmed average derived from the data is determined to be 58.30769, representing a more centralized measure compared to a simple arithmetic mean.

Method 2: Defining Outliers Using the Interquartile Range (IQR)

For analyses requiring a rigorous, statistically defined method of outlier removal, the Interquartile Range (IQR) approach is preferred. The IQR is a measure of statistical dispersion, calculated as the difference between the 75th percentile (Q3, or the third quartile) and the 25th percentile (Q1, or the first quartile). Essentially, it captures the spread of the central 50% of the data.

The standard statistical rule, often attributed to John Tukey, defines a potential outlier as any observation that falls outside the boundaries calculated as 1.5 times the IQR below Q1 or 1.5 times the IQR above Q3. This methodology ensures that only observations deemed statistically unusual, relative to the distribution of the central mass of data, are flagged for exclusion.

This method requires several steps in Google Sheets: first calculating the IQR, then setting the upper and lower fences, using conditional logic to identify specific data points, and finally employing the AVERAGEIF function to perform the calculation on the filtered dataset.

Step-by-Step Calculating the IQR Thresholds

The first critical step is determining the value of the IQR itself. We use the QUARTILE function in Google Sheets, which accepts the data range and an index number (1 for Q1, 3 for Q3). Assuming our data is in range A2:A16, we can calculate the IQR by subtracting the first quartile from the third quartile.

We can place this calculated IQR value into a dedicated cell (e.g., B18) for easy referencing in subsequent formulas. The formula is:

=QUARTILE(A2:A16,3)-QUARTILE(A2:A16,1)

The resulting IQR value, calculated in cell B18, will be used to establish the upper and lower fences that define the boundaries of our acceptable data range. Observations falling outside these fences are statistically classified as potential outliers.

The following screenshot demonstrates the calculation of the IQR:

Identifying and Flagging Outlier Observations

Once the Interquartile Range is established, the next step is to create a parallel column (e.g., Column B, starting in B2) that flags whether each observation in Column A meets the statistical criteria for an outlier. We will use a nested IF and OR function combined with the QUARTILE function to perform this check against the 1.5 * IQR boundary rules.

The formula checks two conditions: whether the value in A2 is less than the Lower Fence (Q1 – 1.5 * IQR) OR whether the value in A2 is greater than the Upper Fence (Q3 + 1.5 * IQR). If either condition is true, the cell returns a “1” (indicating an outlier); otherwise, it returns a “0” (indicating a valid data point).

Note the use of absolute references ($A$2:$A$16 and $B$18) to ensure that the quartile calculation and the IQR reference remain locked when the formula is dragged down the column:

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

Applying this formula across the entire dataset reveals which data points fall outside the 1.5 * IQR range. In our example, after applying this logic to Column B, only one data point is flagged as an outlier, which is the value 164.

The following screenshot shows the result of applying the flagging formula:

Final Calculation using AVERAGEIF

With the outliers successfully identified and marked with a “1” in Column B, the final step is to calculate the average of only those values that are not marked as outliers (i.e., those marked with a “0”). This is achieved efficiently using the AVERAGEIF function.

The AVERAGEIF syntax requires three parameters: the range to check the criteria against (Column B, our flag column), the criterion itself (0, meaning “not an outlier”), and the range containing the values to average (Column A, our original data).

The formula to exclude all flagged outliers is:

=AVERAGEIF(B2:B16, 0, A2:A16)

By executing this formula, Google Sheets calculates the mean of all values in A2:A16 for which the corresponding cell in B2:B16 equals zero. This provides a statistically filtered average that is robust against the influence of the extreme point 164.

The successful execution of the formula yields the following result:

The final average of the dataset, calculated after excluding the statistically defined outlier, is 55.42857. This value is significantly different from the trimmed mean and the raw arithmetic mean, underscoring the impact of outlier methodology choice.

Conclusion: Choosing the Right Method

Both the TRIMMEAN function and the Interquartile Range (IQR) methodology offer effective solutions for mitigating the impact of extreme values on mean calculation in Google Sheets, but they serve different analytical purposes.

The TRIMMEAN method is ideal for quick, exploratory data analysis or when data cleanup must be automated without detailed statistical inspection. It relies on a predetermined decision (e.g., exclude the top and bottom 10%) rather than data distribution characteristics.

Conversely, the IQR approach provides a statistically justifiable definition of an outlier. It is preferred when presenting results for formal reporting or research, as the criteria for exclusion are derived directly from the distribution of the dataset itself. Although it requires more steps (calculating quartiles, IQR, and using AVERAGEIF), the resulting average is based on a robust statistical framework.