Exclude outliers in average calculation excel

**mkvassh** · 10-14-2009, 10:15 AM

Is there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.

The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier.
A user defined function is OK with me, if it is impossible to use the built-in functions. Any suggestion?

**shg** · 10-14-2009, 10:24 AM

See if TRIMMEAN suits your purpose.

**mkvassh** · 10-15-2009, 06:09 AM

I have tried TRIMMEAN on the above range, but it give me 7,10 if I use 20 %. I have tried to goal seek an exclude-percent which give me 6,55, but it will not find any. This function will also remove number from the bottom and the top.

Average without 1000 on the above range is 6,55 and I hope to end up there.

**DonkeyOte** · 10-15-2009, 07:01 AM

I think you need to decide on what you want to base the "outlier" value upon ?

**mkvassh** · 10-15-2009, 07:13 AM

The array function below will remove all outliers if I set the threshold "manually", but then will my next problem be to find the threshold-amount. I have used 1000 as the threshold in my example, but the function will also work with 13 which is not an outlier.

=AVERAGE(IF(ABS(A1:A12)<1000,A1:A12))

I need by other word a function who determine an outlier: I combine that with the function above.

**DonkeyOte** · 10-15-2009, 07:18 AM

I need by other a function who determine an outlier in addition to this.

Well yes, that was my question.... what logic do you want to use ? (ignore the functions for now)

**mkvassh** · 10-15-2009, 08:36 AM

I have used the information in the link below, combined with my function above. It works. Thank you :-)

http://www.tech-archive.net/Archive/.../msg00828.html