#DIV/0! issue - cant get rid of it within another formula

[skymaster]

Hello all
This looks like a forum I will frequent from now on :P

Im trying to get rid of the #DIV/0! error. I want a '0' instead so as the rest of my calculations will work. However this needs to be achieved whilst also having an =average function.

ie: I need an average of the figures in a1:a7 to appear in a8, but if theres no data in a1:a7 then I need a8 to read '0'

I cannot populate the empty cells in a1:a7 with a '0' either - I wish I could, but for this project I cant.

I hope this makes sense - im doing my head in over this.

Thanks heaps in advance!

[sweep]

Hi,

Try this in A8

=IF(ISERROR(AVERAGE(A1:A7)),"0",AVERAGE(A1:A7))

[skymaster]

I was soooo close - just had a few things muddled up - thanks so much for helping - its perfect! **grovels prefusly*

[daddylonglegs]

Here's a couple of methods you could employ.....

=SUM(A1:A7)/MAX(1,COUNT(A1:A7))

or

=IF(COUNT(A1:A7),AVERAGE(A1:A7),0)

[skymaster]

how on earth do you guys get so good at this - its amazing! Thanks again!