Thanks!
Problem trying to solve: Creating a formula for reporting that will eliminate outliers based upon the percentage selected (5% is arbitrary) of outliers to exclude. I would like the formula to not require changes other than % to exclude. The problem with the trimmedmean formula on it's own is that it rounds. In certain cases, it will not exclude the outliers (tail ends) we want it to.
The formula you provided worked great to achieve 12.58, but I think we would need to change 2.1 each time if I used it =TRIMMEAN(array,MAX(2.1/COUNT(array),5%))
Questions on this formula: =TRIMMEAN(array,MAX(2.1/COUNT(array),5%))
Does 2.1 need to be changed for each data set to be the numbers of cells to exclude? - Example: Using 25% instead of 5%, (0.25*14), it should exclude 3.5 from each end. If I round up to 4 using the original data set, the trimmed mean would be 11.83 =AVERAGE(9,10,10,10,15,17)
7 7 8 9 9 10 10 10 15 17 17 19 20 53
- If I change the formula to =TRIMMEAN(array,MAX(8.1/COUNT(array),25%)) I will get 11.83; however, I would like to not have to change 2.1 to 8.1 each time.
- I also noticed that changing using 5% instead of 25% =TRIMMEAN(array,MAX(8.1/COUNT(array),5%)) still results in 11.83
Essentially I want the results of your formula =TRIMMEAN(array,MAX(2.1/COUNT(array),5%)) to calculate automatically by not having to calculate and change 2.1 or 8.1 or etc.. I would like to simply change 5% to 25% and it update the results accordingly.
I hope this makes sense. Thank you!
Bookmarks