Do not display "#DIV/0!", default to 0?

[VBA_Newbie]

Hi folks,

Instead of showing "#DIV/0!" when a formula has as the denominator a 0, to just show 0 instead in the cell? All other cells where the formula does not divide by 0 should simply display normally. Is it possible?

Many thanks!

 

[Smitty]

You can test for 0:

=IF(B1=0,0,A1/B1)

HTH,

Smitty

 

[VBA_Newbie]

Thanks Smitty!

 

[lu_argenitna]

or you can also use

=if(iserror(a1/b1;0;a1/b1))
this formula cna be use to combine any other formula that can show you an error (n/a, valor!, etc) Is very useful!!

 

[Smitty]

or you can also use

=if(iserror(a1/b1;0;a1/b1))
this formula cna be use to combine any other formula that can show you an error (n/a, valor!, etc) Is very useful!!

Just a note that it can be potentially negative as it traps all errors and can lead you to assume that formulas are working properly when they're not.

It also forces 2x calculation, so for longer formulas it can start getting pretty expensive.

Smitty