How to use the TRIMMEAN function
What is the TRIMMEAN function?
The TRIMMEAN function calculates the mean of the interior of a data set. The function excludes a percentage of data points from the top and bottom tails of a data set, use it to exclude outlying data from your analysis.
Table of Contents
1. Introduction
What is trim?
Trim refers to excluding specific values from a group of values, in this case the top and bottom values.
What is the mean?
The arithmetic mean is calculated by dividing the sum of all values by the number of values.
For example, an array contains these values: 3,2,1
The sum is 3 + 2 + 1 equals 6
The number of values is 3.
6/3 equals 2. The average of 3, 2, 1 is 2
What is the interior of a data set?
In statistics and data analysis, the interior and tails refer to different regions within the distribution of a data set:
Interior - The interior of a data set refers to the main body or central region, excluding the outer extremities. It contains the bulk of the values clustered around the center of the distribution (e.g. near the median).
What are the bottom and top tails of a data set?
Bottom/Left Tail - The bottom or left tail encompasses the lowest, smallest values that extend leftwards from the main body. This includes extreme low outliers.
Top/Right Tail - The top or right tail encompasses the highest, largest values that extend rightwards from the main body. This includes extreme high outliers.
Does the TRIMMEAN function exclude the same number of data points from top and bottom?
The TRIMMEAN function rounds the number of excluded data points to the nearest multiple of 2 to create symmetry and exclude the same number of data points from top and bottom.
Example, if there are 100 data points and the percentage is 0.01 then 2 values are excluded even though 0.01 * 100 = 1.
What is the nearest multiple of 2?
The nearest multiple of 2 to a number refers to the closest integer that is divisible by 2.
Some examples of nearest multiple of 2 to
- 5 is 6
- 19 is 20
- 14 is 14
2. TRIMMEAN Function Syntax
TRIMMEAN(array, percent)
3. TRIMMEAN Function Arguments
| array | Required. The values to trim and average. |
| percent | Required. The fractional number of data points to exclude from the calculation. If percent is 0.2 and there are 100 data points, 20 data points are excluded. |
What is a fractional number?
A fractional number represents a part or portion of a whole number. It expresses a ratio of two integers and consists of a numerator and denominator separated by a fraction bar. For example: 1/8, 6/19, 2/39.
4. TRIMMEAN Function Example 1
A manufacturing process produces a batch of components, and quality control measures the dimensions of each component. To assess the overall quality, the engineer wants to calculate the mean dimension while excluding the top 4% and bottom 4% of measurements, which may be due to measurement errors or defective components. How can the TRIMMEAN function help in this situation?
The data is displayed in cell range B19:B68, here are the data points:
| 68 | 79 | 80 | 70 | 80 |
| 71 | 70 | 95 | 89 | 48 |
| 70 | 100 | 80 | 51 | 84 |
| 83 | 59 | 76 | 59 | 52 |
| 64 | 115 | 65 | 65 | 76 |
| 62 | 86 | 50 | 58 | 71 |
| 91 | 87 | 77 | 81 | 70 |
| 81 | 64 | 37 | 63 | 99 |
| 66 | 91 | 76 | 63 | 71 |
| 70 | 72 | 88 | 68 | 92 |
The arguments are:
- array = B19:B68
- percentage = 0.08 (8%)
The data contains 50 individual data points. 8% of 50 equals 4. This means that the two largest and two smallest data points are excluded from the calculation.
The two smallest data points are: 37 and 48. The two largest data points are 115 and 100. These four combined values are excluded.
Formula in cell E21:
The result in cell E21 is 73.54. Lets see if this number is correct. If we exclude values 37, 48, 100, and 115 we get a mean of 73.54
68+71+70+83+64+62+91+81+66+70+79+70+0+59+0+86+87+64+91+72+80+95+80+76+65+50+77+76+88+70+89+51+59+65+58+81+63+63+68+80+84+52+76+71+70+99+71+92 equals 3383
3383 / 46 equals 73.54 The calculation is correct.
The displayed visual representation is a histogram, which illustrates the distribution of the data by categorizing it into distinct value ranges known as bins. Each bin represents a specific interval within which data points may fall. The vertical axis, or the y-axis, depicts the frequency or count of data points that belong to each corresponding bin. In other words, the height of each bar on the histogram indicates the number of observations or occurrences that lie within the specified bin's range.
Number 37 is in the leftmost bin, 48 is in the second bin from the left. Number 100 is in the 7th bin and the largest value of them all 115 is in the last (8th) bin.
Cells E26:E27 calculate the largest and second largest value and cells F26:F27 calculate the smallest and next smallest value, from cell range B19:B68.
5. TRIMMEAN Function Example 2
A company tracks the daily sales of its products. To get a more representative average, the sales manager wants to calculate the mean daily sales while excluding the top 5% and bottom 5% sales figures, which may be influenced by unusual events. How can the TRIMMEAN function help in this scenario?
The data is shown in cell range B19:B43, here are the data points:
| 126 | 101 | 172 | 268 | 139 |
| 132 | 142 | 64 | 101 | 128 |
| 89 | 139 | 121 | 75 | 146 |
| 206 | 117 | 273 | 190 | 107 |
| 111 | 105 | 112 | 153 | 146 |
The arguments are:
- array = B19:B43
- percentage = 0.1 (10%)
The data contains 50 individual data points. 10% of 25 equals 2.4. The nearest multiple of 2.4 is 2. This means that the largest and smallest data points are excluded from the calculation.
The smallest data points is 64 and the largest data points is 273, these two combined values are excluded.
Formula in cell E21:
The result in cell E21 is 135.91. Lets see if this number is correct. If we exclude values 37, 48, 100, and 115 we get a mean of 135.91
126+132+89+206+111+101+142+139+117+105+172+121+112+268+101+75+190+153+139+128+146+107+146 equals 3126
3126 / 23 equals 135.91. The calculation is correct.
The image above shows a histogram which displays the distribution of the data grouped into bins. A bin is an interval that data points may fall into. The y axis shows the frequency of each bin meaning how many data points that fall into each specified bin.
Cell E26 calculates the largest value and cell F26 calculates the smallest value, from cell range B19:B43.
6. How is the TRIMMEAN Function calculated?
Formula in cell D3:
The values are: 62, 46, 189, 272, 145, 276, 187, 40, 153, and 321 and the count is 10.
40% of 10 is 4, the two largest and the two smallest values are excluded. The two top values are: 321 and 276 and the bottom values are 40 and 46.
The average of the remaining values is 62 + 189 + 272 + 145 + 187 + 153 = 1008 , 1008 / 6 equals 168
Functions in 'Statistical' category
The TRIMMEAN function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form