TRIMMEAN function. Average with extreme values (outliers/abnormal values) removed.(Microsoft Excel)
The TRIMMEAN function is a function that removes outliers and outliers, which are the weak points of averages.
For example, if there are 4 cases of 300 and 1 case of 1,000, the result will be 440 when the average of the data is calculated.
In other words, the result is heavily influenced by outliers.
TRIMMEAN averages the data by truncating the upper and lower values by a certain threshold value.
How it works
=TRIMMEAN(Array,Percent)
| Name | Omission | Specify |
|---|---|---|
| Array | Required | The number from which the average is calculated. |
| Percent | Required | Specify the truncation percentage in decimals. For example, 0.2 (20%) means that the top 10% and the bottom 10% are truncated. |
Example Results
This is an example of using the TRIMMEAN and AVERAGE functions.
If 0.4 (40%) is specified for the percentage, the average is calculated by removing 20% (2 pieces) of the top and the bottom of the list, respectively. (Only the yellow background color of the data is used.)
Comparison with MEDIAN function
The median is also an indicator that corresponds to the weakness of the mean to outliers.
Depending on the application, it is often recommended to use the MEDIAN function rather than TRIMMEAN.
This is because the MEDIAN function is simpler and the median is more commonly used.
Also, there is no need to consider the truncation ratio (argument 2).
Spill
Spill when using the BYROW or BYCOL function.
=BYROW(A1:G10,LAMBDA(r,TRIMMEAN(r,0.2)))
=BYCOL(A1:G10,LAMBDA(c,TRIMMEAN(c,0.2)))
---