Table of Contents
Excel is an incredibly powerful tool that allows users to analyze and visualize data in meaningful ways. One of the most useful features of Excel is its ability to calculate averages and exclude the highest and lowest values. This allows users to get a more accurate representation of their data, as outliers can skew the results. This tutorial will show you how to use Excel to calculate an average and exclude the highest and lowest values. By the end of this tutorial, you will have a better understanding of how to use Excel for data analysis.
You can use the following formula in Excel to calculate the average value in a range while excluding the highest and lowest values:
=TRIMMEAN(A2:A11,2/COUNT(A2:A11))
This particular formula drops the highest and lowest values from the range A2:A11 and then calculates the average of the remaining values.
The following example shows how to use this formula in practice.
Example: Calculate Average and Exclude Highest and Lowest Values in Excel
Suppose we have the following column of values in Excel:
Suppose we would like to calculate the average of the range A2:A11 while excluding the highest and lowest values.
We can type the following formula into cell C2 to do so:
=TRIMMEAN(A2:A11,2/COUNT(A2:A11))
The following screenshot shows how to use this formula in practice:
We can see that the average value in the range A2:A11, excluding the highest and lowest values, is 21.
We can verify this is correct by identifying 10 as the lowest value and 44 as the highest value:
The average of the remaining values is calculated as:
Average = (14 + 19 + 22 + 24 + 30 + 28 + 17 + 14) / 8 = 21
How This Formula Works
Recall the formula that we used to calculate the average while excluding the highest and lowest values:
=TRIMMEAN(A2:A11,2/COUNT(A2:A11))
This formula uses the TRIMMEAN function, which uses the following basic syntax:
TRIMMEAN(array, percent)
where:
- array: The range of values to trim and calculate average for
- percent: The percentage of data to trim from range before calcualting average
In this particular example, we use 2/COUNT(A2:A11) to tell Excel to trim 2/10 = 20% of the total values from the range.
This causes the TRIMMEAN function to trim the lowest 10% of values and highest 10% of values from the range.
Since there are exactly 10 values in the range, this trims (i.e. “removes”) the lowest value and the highest value before calculating the average.
Note that this formula will work with a range of any size.
For example, suppose our range had 20 values instead in the range A2:A21. The formula 2/COUNT(A2:A21) would trim 2/20 = 10% of values from the range.
Since there are 20 values in the range, this trims the lowest 5% of values (which is just one value) and the highest 5% of values (which is also just one value) before calculating the average.
Note: You can find the complete documentation for the TRIMMEAN function .
In conclusion, the TRIMMEAN function can be used in Excel to calculate the average of a range while excluding the highest and lowest values. This particular formula works with any range of values and can be used to calculate the average with a specified amount of trimming of the data. The example given in this article demonstrates the use of this formula in practice.