DIV/0 Error

[John C. Harris, MPA]

This is going to seem really simple for you all but I need some guidance
here.

I have two cells with numbers. In the next cell these two divide to give me
a percentage. At the end of that column I have a formula for calculating the
average (ex. =Average A1:G1).

The problem is that if the two cells are empty, it gives me the #DIV/0 in
the formula cell, which then in turn gives me the #Div/0 in the Average
Cell. If I put in false numbers, it gives me the correct percentage in that
row, but still, because some of the rows are not filled, the average formaul
does not work. A way around this is to use the formulka only in the rows
with data, but I know there is another way to do this.

Can anyone help please?

 

[Harald Staff]

=IF(COUNTA(A1:G1)>0,AVERAGE(A1:G1),"No values")
Replace "No values" with number 0 if you need the cell for dependant
calculations.

For the percentage, same logic:
=IF(B5>0,B5/A5,"No values")

 

[John C. Harris, MPA]

Thanks. That did the trick.