Morning All,
I am trying to remove some outliers from a large amount of data, currently i am using the TRIMMEAN function and it seems to be working okay.
jus out of interest is there an alternative way to change the percentage of outliers that are removed.
With the TRIMMEAN is will take say 10% off the top and bottom, but i was wondering if you can take say 10% off the top and 30% off the bottom.
Any help is greatly appreciated.
kind regards
According to the help: "TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN excludes a single value from the top and bottom of the data set".
Using 40 data points as an example, how many data points would you want to exclude from the top and bottom? I am guessing 2 and 6 respectively. Is that correct?
Someone may be in a better position to help if you could provide a file as described in the banner at the top of the page.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Here's a TRIMMEAN workaround:
=AVERAGE(AGGREGATE(15,6,$A$1:$A$20,ROW($4:$15)))
For the 20 values in A1 to A20, the first 3 and last 5 are excluded, as a result of the bit in red.
see sheet for context and for verification.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
FYI, TRIMMEAN has nothing to do with ignoring outliers. Nor does your goal of arbitrarily ignoring the bottom 30% and top 10%.Originally Posted by DaleJ92
For a statistical approach, I would suggest using the interquartile range (IQR) method. LMK if you want details.
I would not use the standard deviation (sd) method unless you have a normal distribution.
For info... I wasn't commenting on the statistical validity of the approach, only on an implementation strategy.
I have doubts about "deskewing" data. If it's skewed, it's skewed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks