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:

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

This matches the value calculated by our formula.

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 here.