dont want #DIV/0!

[lprenrut]

Hi all

Probably easy if you know but I dont, can someone help,here goes

In cells B4:B10 I have numbers 1,2,3 etc...

In cell B11 I have an average of these cells B4:B10

Problem I'm having is if all cells from B4:B10 are blank B11 returns #DIV/0!

I just want B11 to remain empty when cells B4:B10 are blank


Thanks

lprenrut

 

[Andrew Poulsom]

Try:

=IF(COUNT(B4:B10),AVERAGE(B4:B10),"")

 

[VoG]

Try

=IF(COUNT(B4:B10)=0,"",AVERAGE(B4:B10))

 

[CWatts]

Excel 2007+: =IFERROR(AVERAGE(B4:B10),"")
Older Versions of Excel =IF(ISERROR(AVERAGE(B4:B10)),"",AVERAGE(B4:B10))

 

[lprenrut]

That works brilliantly thanks to everyone, but just 1 more favour in cell B13 I have a formula =Round(B12/B11,0)

Also this returns #value! when the cells B4:B10 are blank as with the #DIV/0! I dont want the #value!

Thanks

 

[damianhuerta]

=iferror(round(b12/b11,0),"")

 

[lprenrut]

DAMIANHUERTA

Thank you very much

 

[lprenrut]

Thanks for the formula=IFERROR(ROUND(B12/B11,0),"") for removing the #value! in cell B13, but what I forgot to mention is that I was working with excel 2007 and it doesn't seem to recognise this formula in excel 2003, is there another formula that will work in excel 2003 that will result in the same outcome

Thanks

lprenrut

 

[VoG]

Try

=IF(ISERROR(ROUND(B12/B11,0)),"",ROUND(B12/B11,0))

 

[lprenrut]

I forgot to mention in excel 2003 I get xlfn before =IFERROR(ROUND(B12/B11,0),"") in the formula bar

Thanks
lprenrut