Excel Formula to Calculate Average Without Outliers

When working with data in Microsoft Excel, calculating averages is a common task. However, sometimes your data may contain outliers – values that are significantly higher or lower than most of the data points. These outliers can skew the average and make it less representative of the typical values in your dataset. In this article, we’ll show you how to calculate the average in Excel while excluding outliers using various methods.

Understanding Averages and Outliers

Before we dive into the methods for calculating averages without outliers, let’s briefly review what these terms mean:

• An average (or arithmetic mean) is the sum of a set of values divided by the number of values. It represents the central tendency or typical value of the dataset.
• An outlier is a data point that differs significantly from other observations. Outliers can be much higher or lower than the majority of values in the dataset.

Outliers can occur for various reasons, such as data entry errors, measurement issues, or genuine extreme values. While outliers may be interesting to investigate, they can distort the average and lead to misleading conclusions if not handled appropriately.

Why Exclude Outliers When Calculating the Average?

Outliers can have a significant impact on the average, pulling it towards the extreme value and making it less representative of the central tendency of the data. By excluding outliers, we can calculate an average that better reflects the typical values in the dataset.

For example, let’s say you have the following dataset of salaries:

The last value, $500,000, is an outlier that is much higher than the other salaries. If we calculate the average salary using the standard AVERAGE function in Excel, we get:

=(50000+55000+60000+65000+500000)/5
=$146,000

The average salary of $146,000 is not a good representation of the typical salary in this dataset, as it is skewed upwards by the outlier. By excluding the outlier, we can calculate an average that better captures the central tendency of the salaries.

Method 1: Using the TRIMMEAN Function

Excel provides a built-in function called TRIMMEAN that allows you to calculate the mean of a dataset while excluding a specified percentage of data points from the top and bottom of the sorted values. Here’s how to use it:

1. Arrange your data in a single column or row.
2. In a cell where you want the result to appear, type =TRIMMEAN(
3. Select the range of cells containing your data.
4. Type a comma (,), and then enter the percentage of data points you want to exclude from each end of the sorted values (e.g., 0.1 for 10%).
5. Close the parentheses and press Enter.

For example, to calculate the average of the salaries while excluding the top and bottom 10% of values, you would use:

=TRIMMEAN(A2:A6, 0.1)

In this case, the TRIMMEAN function sorts the salaries, removes the top and bottom values (10% of 5 data points is 0.5, rounded up to 1), and calculates the mean of the remaining values.

The TRIMMEAN function is useful when you have a large dataset and want to exclude a fixed percentage of extreme values from both ends of the distribution. However, it may not always remove all outliers, especially if they are not in the top or bottom percentiles.

Method 2: Using the AVERAGEIF Function with Criteria

Another way to exclude outliers when calculating the average is to use the AVERAGEIF function with criteria that define the range of acceptable values. Here’s how:

1. Arrange your data in a single column or row.
2. Determine the lower and upper bounds for your acceptable values (e.g., 1st and 99th percentile, or mean ± 2 standard deviations).
3. In a cell where you want the result to appear, type =AVERAGEIF(
4. Select the range of cells containing your data.
5. Type a comma (,), and then enter your criteria using cell references or values (e.g., "B2:B6",">="&C1,"<="&C2 where C1 contains the lower bound and C2 the upper bound).
6. Close the parentheses and press Enter.

For example, let’s say we want to calculate the average of salaries between $40,000 and $70,000. We could use:

=AVERAGEIF(A2:A6,">=40000",A2:A6)-AVERAGEIF(A2:A6,">70000",A2:A6)

This subtracts the average of salaries greater than $70,000 from the average of salaries greater than or equal to $40,000, effectively giving us the average of salaries between $40,000 and $70,000.

The AVERAGEIF function allows you to specify precise criteria for inclusion in the average calculation. However, it requires you to determine appropriate lower and upper bounds, which may involve some data exploration and statistical analysis.

Method 3: Using an Array Formula

Array formulas allow you to perform calculations on multiple values and return a result. Here’s how to use an array formula to calculate the average excluding outliers:

1. Arrange your data in a single column or row.
2. Determine the lower and upper bounds for your acceptable values.
3. In a cell where you want the result to appear, type =AVERAGE(IF(
4. Select the range of cells containing your data.
5. Type >lower_bound,
6. Select the range of cells again.
7. Type ,IF(
8. Select the range a third time.
9. Type <upper_bound,
10. Select the range a fourth time.
11. Type )))
12. Instead of pressing Enter, hold down Ctrl+Shift and press Enter to enter the formula as an array.

For example, to calculate the average of salaries between $40,000 and $70,000, you would use:

=AVERAGE(IF(A2:A6>40000,A2:A6,IF(A2:A6<70000,A2:A6)))

The IF functions check each value in the range A2:A6 to see if it’s greater than $40,000 and less than $70,000. If both conditions are true, the value is included in the AVERAGE calculation. The result is the average of salaries within the specified range.

Important: Remember to enter array formulas using Ctrl+Shift+Enter, not just Enter. Excel will automatically add curly braces {} around the formula to indicate it’s an array.

Array formulas offer flexibility in defining complex criteria for inclusion in the average. However, they can be more difficult to set up and modify compared to regular formulas.

Tips for Identifying and Handling Outliers

Deciding what constitutes an outlier and how to handle it depends on your data and analysis goals. Here are some tips:

• Create a box plot, histogram, or scatter plot of your data to visually identify potential outliers. These charts can help you spot values that are far from the main cluster of data points.
• Calculate statistical measures like z-scores, interquartile range (IQR), or Tukey fences to objectively define outliers. These methods compare each value to the center and spread of the dataset to determine if it’s an unusual observation.
• Consider the context and subject area. Unusually large or small values may be legitimate data points that shouldn’t be discarded. For example, in a dataset of housing prices, a multi-million dollar mansion may be a genuine outlier rather than a data entry error.
• If outliers are due to data entry errors, try to correct them if possible. Check the original data source and make necessary adjustments. If the outliers are due to measurement issues, consider re-collecting the data to ensure accuracy.
• Document how you identified and handled outliers so others can understand and evaluate your analysis. Transparency is key to reproducibility and credibility in data analysis.

Final Thoughts

Outliers can have a significant impact on averages in Excel, making them less representative of typical values. By using functions like TRIMMEAN, AVERAGEIF with criteria, or array formulas, you can calculate averages that exclude outliers and better capture the central tendency of your data.

By mastering these techniques for calculating averages without outliers, you’ll be able to analyze your data more effectively, draw meaningful insights, and make better-informed decisions. Excel provides powerful tools for data analysis, and understanding how to handle outliers is a crucial skill for any data analyst or business professional.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.