AVERAGE formula with a twist

[Phil Wagner]

Hello -

I need to average a range of cells B2:Y31. Normally, I'd use the formula
=AVERAGE(B2:Y31)... easy enough.

The twist is that I need to average that range of cells, BUT any cell that
has a value less than 1 or more than 900 cannot be used in the calculation.
These values are considered invalid for my purposes.

All cell values are referencing a cell value in another worksheet that is
part of the spreadsheet; for instance =bam!B2

How can I use a formula to automatically calculate the average of the range
without calculating the invalid values?

TIA!

 

[Peo Sjoblom]

=AVERAGE(IF((B2:Y31>=1)*(B2:Y31<=900),B2:Y31))


entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom

 

[Phil Wagner]

Peo -

When I tried using this formula Excel returned a #VALUE! in the cell.

The tiny exclamation icon said "A value used in the formula is of the wrong
data type"

--
---
Phil

=AVERAGE(IF((B2:Y31>=1)*(B2:Y31<=900),B2:Y31))


entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom

 

[Phil Wagner]

Peo - It works!!! I forgot to enter it with Ctl-Shift-Enter as you
indicated.

Thanks!

---
Phil

=AVERAGE(IF((B2:Y31>=1)*(B2:Y31<=900),B2:Y31))


entered with ctrl + shift & enter

Regards,


Peo Sjoblom

 

[ribitt]

I am new to this, and the formula looks correct to me. But .....

Why do you use ctrl+shift & enter, rather than just a standard enter?

Thanks

Ribitt

 

[Peo Sjoblom]

http://www.cpearson.com/excel/ArrayFormulas.aspx


--


Regards,


Peo Sjoblom