Special function

[Bart Steur]

Hi,

I have a range of cells (100+), which should all have a value of around
3600, so the average should also be around 3600 (minimum should not be lower
then 300, maximum shouldn't be higher than 10000). But sometimes some cells
contain values of -2000000 or +/-2 or +2000000. So when I calculate the
average (using the AVERAGE Function) I get abnormal results.

Is there a function that can automaticly reconize the excessive values and
exclude them from the Average calculation.

Thanks,

Bart

[Bernie Deitrick]

Bart,

Array enter using Ctrl-Shift-Enter:

=AVERAGE(IF((A1:A100>300)*(A1:A100<10000),A1:A100))

Change range to match your actual values.

HTH,
Bernie
MS Excel MVP


"Bart Steur" <solnews@xs4all.nl> wrote in message news:ezoDpHIDGHA.336@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> I have a range of cells (100+), which should all have a value of around 3600, so the average
> should also be around 3600 (minimum should not be lower then 300, maximum shouldn't be higher than
> 10000). But sometimes some cells contain values of -2000000 or +/-2 or +2000000. So when I
> calculate the average (using the AVERAGE Function) I get abnormal results.
>
> Is there a function that can automaticly reconize the excessive values and exclude them from the
> Average calculation.
>
> Thanks,
>
> Bart
>

[Bernard Liengme]

This works for me
=SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000),A1:A100)/SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bart Steur" <solnews@xs4all.nl> wrote in message
news:ezoDpHIDGHA.336@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> I have a range of cells (100+), which should all have a value of around
> 3600, so the average should also be around 3600 (minimum should not be
> lower then 300, maximum shouldn't be higher than 10000). But sometimes
> some cells contain values of -2000000 or +/-2 or +2000000. So when I
> calculate the average (using the AVERAGE Function) I get abnormal results.
>
> Is there a function that can automaticly reconize the excessive values and
> exclude them from the Average calculation.
>
> Thanks,
>
> Bart
>

[Bart Steur]

There is no statistical/analitical function to do this. A function that
recognizes high or low values compared to the rest and excludes them?

Bart


"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:eRVdfQIDGHA.2320@TK2MSFTNGP12.phx.gbl...
> This works for me
> =SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000),A1:A100)/SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000))
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bart Steur" <solnews@xs4all.nl> wrote in message
> news:ezoDpHIDGHA.336@TK2MSFTNGP14.phx.gbl...
>> Hi,
>>
>> I have a range of cells (100+), which should all have a value of around
>> 3600, so the average should also be around 3600 (minimum should not be
>> lower then 300, maximum shouldn't be higher than 10000). But sometimes
>> some cells contain values of -2000000 or +/-2 or +2000000. So when I
>> calculate the average (using the AVERAGE Function) I get abnormal
>> results.
>>
>> Is there a function that can automaticly reconize the excessive values
>> and exclude them from the Average calculation.
>>
>> Thanks,
>>
>> Bart
>>
>
>

[Bernard Liengme]

No, there is no built-in function that knows about 'outliers'

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bart Steur" <solnews@xs4all.nl> wrote in message
news:%23hWxEMTDGHA.2320@TK2MSFTNGP11.phx.gbl...
> There is no statistical/analitical function to do this. A function that
> recognizes high or low values compared to the rest and excludes them?
>
> Bart
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:eRVdfQIDGHA.2320@TK2MSFTNGP12.phx.gbl...
>> This works for me
>> =SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000),A1:A100)/SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000))
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Bart Steur" <solnews@xs4all.nl> wrote in message
>> news:ezoDpHIDGHA.336@TK2MSFTNGP14.phx.gbl...
>>> Hi,
>>>
>>> I have a range of cells (100+), which should all have a value of around
>>> 3600, so the average should also be around 3600 (minimum should not be
>>> lower then 300, maximum shouldn't be higher than 10000). But sometimes
>>> some cells contain values of -2000000 or +/-2 or +2000000. So when I
>>> calculate the average (using the AVERAGE Function) I get abnormal
>>> results.
>>>
>>> Is there a function that can automaticly reconize the excessive values
>>> and exclude them from the Average calculation.
>>>
>>> Thanks,
>>>
>>> Bart
>>>
>>
>>
>
>

[Ron Rosenfeld]

On Fri, 30 Dec 2005 12:20:17 +0100, "Bart Steur" <solnews@xs4all.nl> wrote:

>There is no statistical/analitical function to do this. A function that
>recognizes high or low values compared to the rest and excludes them?
>
>Bart
>
>

Take a look at HELP for the TRIMMEAN function. This can help you eliminate
outliers.


--ron