Average

[patboy]

I am trying to average a block of cells (=Average(g2:g52) but a few o
the cells contain formulas that do not have values yet. Any ideas o
how to get the average work, or omit certain cells?
Thank

 

[Don Guillett]

try either of these ARRAY formulas (enter/edit with control/shift/enter)
=AVERAGE(IF(E1:E5<>0,E1:E5))
or
=AVERAGE(IF(E1:E5<>"",E1:E5))

 

[patboy]

Thanks for the idea, but I still get a "#DIV/0!" symbol. Any ideas o
how to bypass that?

Thank

 

[Mark Wolven]

I am trying to average a block of cells (=Average(g2:g52) but a few of
the cells contain formulas that do not have values yet. Any ideas on
how to get the average work, or omit certain cells?
Thanks

It might be easier to change the way the formulas w/o values are
displayed.

For example, you can make add if/then logic to a statement. If your
formula divides by zero, you will get an #DIV/0! error. You can get by
that with something like this:

=If(c1=0,"",b1/c1)

That way you'll never have a #div/0 error, instead the formula places
"" in the cell, which will evaluate as a blank in your formula - and
be ignored. That is until the formula no longer returns the #div/0
error.

That help?

 

[Jerry W. Lewis]

=IF(COUNT(G2:G52)=0,whatever,AVERAGE(G2:G52))

Jerry