Excel: divide by zero error #DIV/0!

[basildon]

Hi

When I divide by a cell reference value that is zero I get the following message #DIV/0!.

I understand the problem but for chart purposes would like this to be shown as 0 (zero).

ctrl H doesn't work unless I paste as values. Wondering if anyone had a formula or something similar.

Cheers

 

[Lilliabeth]

YOu can wrap the original formula that returns div/0 inside IF and ISERROR.

For example:

=IF(ISERROR(AVERAGE(A1:A20),0,AVERAGE(A1:A20))

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[bluedragon2]

=IF(ISERR(your function),0,your fundtion)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Welshbird]

I would have suggested something similar:

=if(b1=0,0,a1/b1)

if you are dividing a by b.

I guess we all get the same result though?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]