calculate mean without outliers
 

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.
--
maryj

calculate mean without outliers
 

Look up the TRIMMEAN function in Excel Help.

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

Regards,

Ron
Microsoft MVP (Excel)


"maryj" wrote in message
...
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.
--
maryj

calculate mean without outliers
 

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.


On Sep 6, 5:10 pm, maryj wrote:
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.
--
maryj

calculate mean without outliers
 

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.

On Sep 7, 10:38 am, iliace wrote:
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.

On Sep 6, 5:10 pm, maryj wrote:



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.
--
maryj- Hide quoted text -

- Show quoted text -