AVERAGE formula with a twist

  • Thread starter Thread starter Phil Wagner
  • Start date Start date
P

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!
 
=AVERAGE(IF((B2:Y31>=1)*(B2:Y31<=900),B2:Y31))


entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom
 
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
Peo Sjoblom said:
=AVERAGE(IF((B2:Y31>=1)*(B2:Y31<=900),B2:Y31))


entered with ctrl + shift & enter


--


Regards,


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

Thanks!

---
Phil

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


entered with ctrl + shift & enter

Regards,


Peo Sjoblom
Click to expand...
 
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
 
Back
Top