Special function

B

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
 
B

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
 
B

Bernard Liengme

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

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
 
R

Ron Rosenfeld

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
Click to expand...

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


--ron
 
Top