Average

  • Thread starter Thread starter patboy
  • Start date Start date
P

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
 
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))
 
Thanks for the idea, but I still get a "#DIV/0!" symbol. Any ideas o
how to bypass that?

Thank
 
patboy said:
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
Click to expand...

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?
 
Back
Top