Blank cells are zeros?

[sdlevy13]

I am trying to average cells, and several cells are blank for now. They will eventually be filled in, but they are showing up in the average formula as zeros, so has the #DIV/0! error. How can I get it to not factor in the blank cells?

 

[delaneyjm]

Assuming your data is in A1:A20.

For Excel 2007 and newer:

=AVERAGEIF(A1:A20,">"&0)

For 2003 and older:

=SUMIF(A1:A20,">"&0)/COUNTIF(A1:A20,">"&0)

 

[Jonmo1]

Welcome to the board...

The Average function does not consider blanks as 0's.

Can you post the actual formula in question?

 

[sark666]

That's strange someone else just asked the same thing. The OP is correct though. Averaging an area with no populated values will result in a div/0! error.

Try like so:

=IF(ISERROR(AVERAGE(D4:D6)),"",AVERAGE(D4:D6))

 

[Scott Huish]

or perhaps:

=SUM(D4:D6)/MAX(1,COUNT(D4:D6))