How to Use the TRIMMEAN Function in Excel to Calculate Averages While Ignoring Outliers

When analyzing datasets in Microsoft Excel, calculating a standard average (the arithmetic mean) is not always the best approach. If your data contains extreme outliers—such as unusually high sales figures or abnormally low test scores—the standard AVERAGE function will be skewed, providing an inaccurate picture of the typical result. The TRIMMEAN function solves this by automatically removing a specific percentage of the highest and lowest values before calculating the average.

Understanding the TRIMMEAN Function

The syntax for the function is =TRIMMEAN(array, percent).

• array: The range of cells containing the numbers you want to average.
• percent: The total percentage of data points you want to exclude from the calculation. This percentage is divided equally between the top and bottom extremes. For example, a setting of 0.2 (20%) will trim the top 10% and the bottom 10% of the dataset.

Step 1: Organize Your Data

Before applying the formula, ensure your data is properly structured.

1. Enter your numerical data into a single column. For example, place 20 test scores in cells A1 through A20.
2. Identify any obvious anomalies to determine what percentage of outliers you might want to trim.

Step 2: Write the TRIMMEAN Formula

You can apply the formula in any empty cell.

1. Click on the cell where you want the trimmed average to appear (e.g., C1).
2. Type the beginning of the formula: =TRIMMEAN(
3. Select your data range. Using our example, click and drag from A1 to A20, so the formula reads =TRIMMEAN(A1:A20,
4. Type the percentage of data points you wish to exclude as a decimal. If you want to exclude the highest 10% and lowest 10% (a total of 20%), type 0.2.
5. Close the bracket so the final formula looks like this: =TRIMMEAN(A1:A20, 0.2)
6. Press Enter.

Step 3: Understand How Excel Trims Data

It is important to know exactly how many data points Excel is removing to ensure your sample size remains valid.

1. In a dataset of 20 items, a 20% trim (0.2) equals exactly 4 items.
2. Excel will remove the 2 highest values and the 2 lowest values, calculating the average based only on the middle 16 values.
3. If the calculated trim number is not a whole number, Excel rounds down to the nearest multiple of two. This ensures symmetry, trimming an equal number of high and low values.

Using the TRIMMEAN function is an excellent way to clean up “noisy” data in Excel without having to manually delete extreme values from your spreadsheet.