calculate mean without outliers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to assist a client with this. He needs to find the mean of numbers
with the exception of those that are very different from the rest - outliers.
Can you give us some ideas of how to do this? He is trying to avoid adding
extra helper columns to do this.

Thanks.
 
Look up the TRIMMEAN function in Excel Help.

Will that do what you want?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
 
I'm don't know whether you're trying to impress your client, or avoid
confusing him, but this might work for you. This is array-entered,
meaning you press Ctrl+Shift+Enter instead of just Enter, to confirm.

=AVERAGE(IF(A1:A7*(ABS(A1:A7-MEDIAN(A1:A7))<5)<>0,A1:A7*(ABS(A1:A7-
MEDIAN(A1:A7))<5),""))

The idea is to only take the average of values that are within a
certain range from the median. If you know what constitutes an
outlier relative to the median, the formula above works. In the
above, the average of values within 5 of the median, in A1:A7. In
other words, if A1:A7 looks like this:

4
5
6
3
7
29
17

The above formula returns the average of the first five values,
because 29 and 17 are more than 5 from the median (which is 6). The
IF is necessary to avoid averaging in zeros, because AVERAGE ignores
blanks. Therefore, replace all instances of A1:A7 with the range to
average, and all instances of 5 with the distance from the median.

Hope that makes sense.
 
Sorry! Should be:

=AVERAGE(IF(A1:A7*(ABS(A1:A7-MEDIAN(A1:A7))<5)<>0,A1:A7,""))

Previous version works too, but it's redundant.
 
Back
Top