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,

Array enter using Ctrl-Shift-Enter:

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

Change range to match your actual values.

HTH,
Bernie
MS Excel MVP


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

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

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

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

On Fri, 30 Dec 2005 12:20:17 +0100, "Bart Steur" 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