How can I calculate the average of a set of numbers in Excel while excluding the highest and lowest values?

By /

To calculate the average of a set of numbers in Excel while excluding the highest and lowest values, you can use the AVERAGEIFS function. This function allows you to specify a range of numbers to be included in the calculation, while also excluding certain values based on a criteria. In this case, you can specify the criteria to exclude the highest and lowest values by using the MAX and MIN functions to find the maximum and minimum values in the range, and then using those values as the criteria for the AVERAGEIFS function. This will give you the average of the remaining values in the set.

Excel: Calculate Average and Exclude Highest & Lowest Values

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:

Excel calculate average and exclude highest and lowest values

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 .

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Calculate Average and Exclude Highest & Lowest Values
  2. How to calculate average excluding outliers in google sheets?
  3. How can I use Excel to calculate the average and drop the lowest value from a set of data?
  4. Excel: Calculate Average and Drop Lowest Value
  5. Find the Class with the Highest Average Grade
  6. Excel: Calculate Average of Numbers Separated by Commas
  7. Find Lowest 3 Values in Excel (With Example)
  8. How can I highlight duplicates in Excel, excluding the first instance?
  9. How can I use Google Sheets to calculate the average of a set of numbers while rounding to the nearest whole number?
  10. How do I find the first 3 positive numbers in Excel when I have a column of numbers?