How To Calculate a Trimmed Mean in Excel

This guide will explain how to calculate a trimmed mean in Excel using the TRIMMEAN function

The rules for using the TRIMMEAN function in Excel are the following:

  • The TRIMMEAN function returns the mean of the interior portion of a set of the given data values.
  • When the given data set is not arranged in ascending order, the function will return an error or incorrect result.
  • When the given data set contains text values, the function will return an error or incorrect result.
  • If the given data set contains less than 10 data values, the function will return an error or incorrect result. 
  • The TRIMMEAN function will round the excluded data values down to the nearest multiple of 2. 

Since it has several built-in functions and tools, Excel is a powerful tool that we can utilize to perform many difficult and time-consuming tasks. For example, we can easily analyze data and statistical calculations in Excel. 

In this guide, we will explain how to use the TRIMMEAN function to get the trimmed mean in Excel. So a trimmed mean refers to excluding a specific percentage of the smallest and largest values in the data set and then calculating the mean with the remaining data values. 

Moreover, a trimmed mean is a great way to reduce the impact of outlying values in the data set, which can affect the calculation. Luckily, we can easily calculate the trimmed mean by using the TRIMMEAN function in Excel.

Let’s take a sample scenario wherein we need to calculate a trimmed mean in Excel.

Suppose you have a data set containing a list of numeric values. And you want to calculate the mean for the data set. However, you want to trim 10 percent of the data set to remove the outliers before calculating the mean. 

So you used the TRIMMEAN function to calculate the 10 percent trimmed mean. If you had 30 values in your data set, the function excluded two values from the top and two more values from the bottom of the data set before calculating the mean.

Before we move on to a real example of calculating a trimmed mean in Excel, let’s first understand how to use the TRIMMEAN function.

The Anatomy of the TRIMMEAN Function

The syntax or the way we write the TRIMMEAN function is as follows:

=TRIMMEAN(array, percent)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • TRIMMEAN() refers to our TRIMMEAN function. And this function is used to calculate the average or mean of the given data set while excluding outlying values. Furthermore, the number of data values to exclude or trim is given as a percentage.
  • array is a required argument. So this argument refers to the range or array of values we want to trim and calculate the mean.
  • percent is another required argument. And this argument is the fractional or decimal number of data points to exclude or trim from the data set’s top and bottom. For example, we will input 0.05 to exclude 5 percent.

Great! Now we can dive into a real example of calculating a trimmed mean in Excel using the TRIMMEAN function.

A Real Example of Calculating A Trimmed Mean in Excel

Let’s say we have a data set containing a list of 40 values. Since we want to get the average or mean that accurately represents our data set, we want to calculate the trimmed mean instead of a normal mean. So our initial data set would look like this:

Initial dataset

 

So a trimmed mean refers to a type of mean that has been calculated after excluding a specific or given percentage of the top and bottom values in the data set. And it is similar to a standard or normal mean which we usually calculate using the AVERAGE function in Excel.

However, we will utilize the TRIMMEAN function to calculate the trimmed mean of our data set. Furthermore, the trimmed mean is helpful in quickly removing outlying values that may greatly influence the data set’s mean. 

When we have a data set that contains values that are greatly varied or skewed, it is better to calculate the trimmed mean than a normal mean. And this would also ensure that the mean can accurately represent the values in the data set.

For instance, we want to calculate a 5 percent trimmed mean for our data set. In this case, a 5 percent trimmed mean refers to a data set’s mean after 5 percent of the values from the top and bottom of the data set have been excluded. 

So the TRIMMEAN function can easily calculate the 5 percent trimmed mean. Since we have a total of 40 values in our data set, 5 percent of 40 is 2.5. However, the TRIMMEAN will round the excluded data values down to the nearest multiple of 2. 

In this example, with 40 data values, 5 percent is equal to 2.5 values. So the TRIMMEAN function will round down 2.5 to 2 as it is the nearest multiple of 2. Thus, the function will exclude one value from the top of the data set and another value from the bottom of the data set before calculating the mean.

So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and explain the process of calculating a trimmed mean in Excel using the TRIMMEAN function.

How to Calculate a Trimmed Mean in Excel

In this section, we will explain the step-by-step process of calculating a trimmed mean in Excel using the TRIMMEAN function. Additionally, each step has detailed instructions and pictures for you to follow easily.

To apply this method to your work, we can simply follow the steps below. 

1. Firstly, we need to decide where we want to input the result of our trimmed mean. In this case, we want to get a 5 percent trimmed mean. So we will input the formula “=TRIMMEAN(B2:B41, 0.05)”. Lastly, we will press the Enter key to return the result.

Trimmed Mean In Excel

 

2. If we want to display a specific number of decimal points for our trimmed mean, we can simply decrease the number of decimal places. To do this, we can go to the Home tab and click the Decrease Decimal icon.

Trimmed Mean In Excel

 

3. And tada! We have successfully calculated a trimmed mean in Excel using the TRIMMEAN function.

Trimmed Mean In Excel

 

And that’s pretty much it! We have explained how to calculate a trimmed mean in Excel using the TRIMMEAN function. Now you can easily apply this method to your work whenever you need to exclude the outlying values in your data set from the mean.

Are you interested in learning more about what Excel can do? You can now use the TRIMMEAN function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like