Excel: Calculate Average and Exclude Highest & Lowest Values

The Essential Formula for Trimmed Averaging

To calculate the average of a dataset within Excel while automatically discarding the highest and lowest values—a technique often employed in statistical reporting to reduce variance—you can utilize the robust combination of the TRIMMEAN and COUNT functions. The standard formula structure designed to trim exactly one value from each extreme is presented below:

=TRIMMEAN(A2:A11,2/COUNT(A2:A11))

This powerful configuration specifically targets the data within the defined range (in this case, A2:A11). It instructs Excel to identify and exclude the single largest value and the single smallest value, subsequently calculating the arithmetic mean of the remaining intermediate data points. Understanding this formula is the first step toward performing advanced data analysis without the interference of statistical outliers.

Practical Example: Trimming Extremes from a Data Set

To illustrate the application of the trimmed mean formula, consider a hypothetical column of numerical values, perhaps representing scores, measurements, or financial readings, as displayed in the image below. We aim to determine the central tendency of this data set while consciously removing any influence from the highest and lowest scores.

Suppose we have the following column of values in Excel:

Our objective is clear: calculate the average of the range A2:A11, but only after excluding the single highest and single lowest value present in that array.

Applying the Formula in Practice

Since we are working with the range A2:A11, we will input the necessary formula into an empty cell, such as cell C2, to execute the calculation. This simple step initiates the precise statistical trimming required:

=TRIMMEAN(A2:A11,2/COUNT(A2:A11))

Once entered, Excel processes the formula, yielding the trimmed mean instantly. The following screenshot visually confirms the result of applying this formula to our sample data:

As demonstrated, the resulting average value for the range A2:A11, after the automatic exclusion of the highest and lowest values, is calculated precisely as 21.

Verification: Manual Calculation of the Trimmed Mean

To ensure the formula is functioning correctly and to deepen our understanding of the process, we can manually verify the result. First, we must identify the extreme outliers within the set A2:A11:

• The lowest value in the dataset is 10.
• The highest value in the dataset is 44.

The visual representation below clearly highlights these two excluded values, demonstrating what the TRIMMEAN function effectively ignores before calculating the mean:

By removing 10 and 44, eight values remain: 14, 19, 22, 24, 30, 28, 17, and 14. The average of these remaining values is calculated as follows:

Average = (14 + 19 + 22 + 24 + 30 + 28 + 17 + 14) / 8 = 21

Deconstructing the TRIMMEAN Function Syntax

To fully leverage this technique in future data analysis projects, it is essential to understand the underlying mechanics of the function used. Let us recall the powerful formula used to calculate the average while precisely controlling the exclusion of extreme data points:

=TRIMMEAN(A2:A11,2/COUNT(A2:A11))

This calculation relies entirely on the TRIMMEAN function, which is designed to compute the mean of a data set after a specified percentage of data points are removed from the top and bottom tails of the distribution. Its fundamental syntax is straightforward:

TRIMMEAN(array, percent)

The arguments are defined as follows:

• array: This argument specifies the range of values that Excel should analyze, trim, and ultimately calculate the average for (e.g., A2:A11).
• percent: This is the crucial variable, representing the fractional or percentage quantity of data points to be excluded from both the lowest and highest ends of the range before the average is computed.

Calculating the Exclusion Percentage

In our specific example, we employed the expression 2/COUNT(A2:A11) to dynamically determine the necessary percentage for trimming. Since the range A2:A11 contains exactly 10 values, the COUNT function returns 10. Therefore, the resulting percentage calculation becomes 2/10, or 0.2, which is equivalent to 20%.

When TRIMMEAN receives a 20% instruction, it interprets this as removing 10% of the total values from the bottom (lowest) end and 10% of the total values from the top (highest) end.

Given that 10% of 10 total values is exactly one value, this setup successfully trims (removes) the single lowest value and the single highest value before proceeding with the calculation of the average. This clever use of the COUNT function ensures that the trimming is based on the number of observations rather than a fixed percentage that might not correspond to whole numbers of data points.

Adapting the Formula for Variable Range Sizes

A significant advantage of embedding the COUNT function within the percentage argument is the formula’s inherent flexibility; it operates seamlessly regardless of the size of the input range.

Consider, for instance, a situation where our dataset spanned 20 values instead, covering the range A2:A21. The formula would dynamically adjust to 2/COUNT(A2:A21). Since COUNT would return 20, the calculated percentage is 2/20, or 10%.

In this scenario, TRIMMEAN trims 5% from the lowest end and 5% from the highest end. Since 5% of 20 values is exactly one value, the result remains the same: the lowest value and the highest value are removed before the mean is calculated. This dynamic proportionality makes the construction 2/COUNT(range) the ideal standard for single-value outlier exclusion in Excel.

For comprehensive documentation and additional details on advanced uses, please refer to the official resource for the TRIMMEAN function.