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.
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
TRIMMEAN function example
Formula in cell D3:
40 % of the data points in cell range B3:B12 are excluded from the calculation.
TRIMMEAN function Syntax
TRIMMEAN(array, percent)
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.
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 many 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