calculate mean without outliers

[maryj]

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.

 

[Ron Coderre]

Look up the TRIMMEAN function in Excel Help.

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

Regards,

Ron
Microsoft MVP (Excel)

 

[iliace]

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.

 

[iliace]

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.