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

Look up the TRIMMEAN function in Excel Help.

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

Regards,

Ron
Microsoft MVP (Excel)


"maryj" <(E-Mail Removed)> wrote in message
news:752918F1-CEF0-416D-8609-(E-Mail Removed)...
> 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

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 <ma...@discussions.microsoft.com> 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

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 <iasaf...@gmail.com> 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 <ma...@discussions.microsoft.com> 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 -