The `TRIMMEAN` function in Excel is used to calculate the mean (average) of a data set while excluding a specified percentage of the highest and lowest values. This is particularly useful when you want to calculate an average that is not distorted by outliers.
Here’s how to use the `TRIMMEAN` function:
Syntax
TRIMMEAN(array, percent)
- array: The range or array of numbers from which you want to calculate the trimmed mean.
- percent: The percentage (expressed as a decimal) of data points to exclude from the top and bottom of the data set. For example, if you want to exclude 20% of data points, you’d use 0.2.
Steps to Use TRIMMEAN
=TRIMMEAN(A1:A100, 0.1)
- Choose Your Data: Identify the range of cells that contains the data you want to analyze.
- Insert the Function: Click on the cell where you want the result to appear.
- Enter the Formula: Type the `TRIMMEAN` function into the selected cell. For example:
In this example, the function calculates the trimmed mean of the data in cells A1 through A100, excluding the top and bottom 10% of the data points.
- Press Enter: After entering the formula, press Enter to display the result in the cell.
Example
Suppose you have a list of test scores in cells B2 to B21 and you want to calculate the average score, excluding the most extreme 10% of scores at both ends. You would use the formula:
=TRIMMEAN(B2:B21, 0.1)
Important Notes
- The percent parameter is crucial in defining how many of the smallest and largest data points should be trimmed. It’s expressed as a decimal, where 10% is written as 0.1.
- Excel will round the number of data points to exclude to the nearest whole number.
- If you provide a `percent` value of 0, `TRIMMEAN` will work like the usual `AVERAGE` function as no data will be trimmed.
The `TRIMMEAN` function is particularly useful in datasets with outliers or anomalous results that you don’t want to influence the average calculation too heavily.
Unlock Your Potential
Excel
Access
Power BI
