Error Divide By Zero

  • Thread starter Thread starter diablo
  • Start date Start date
D

diablo

Is there any way to put something in your formula to get the error #DIV/0!
to be ignored? I know that you can change the iterations on the calculation
tab under options. But the formula that I'm using is correct and what I
want. Just wondering if I can suppress the warning to the user.

Thanks,
Brian
 
If you want a formula to not render the error use a combination of IF
and ISERROR. For example:

IF(ISERROR(A1/B1,"",A1/B1)

This says, if A1 divided by B1 returns an error, leave the cell blank
(""), otherwise, divided A1 by B1.

I think there are also ways to ignore errors in calculations (e.g.
taking the average of a column, ignoring cells that contain errors).
If this is what you're looking for please specify in the post.


Jim
 
Assuming you need the formula =A1/B1
Replace it by =IF(ISERROR(A1/B1),"",A1/B1)
or =IF(B1=0,"",A1/B1)
best wishes
 
As a follow up, if you just want to hide the error, but not lengthen
your formula, you can use conditional formatting on the relevant cells
to change the font color of errors to the same as the cell color:

Format > Cells > Conditional Formatting
Where Formula Is:
=ISERROR(INDIRECT(ADDRESS(ROW(),COLUMN())))

Change font color to match background.
 
ISERROR will hide any errors. not just the Divide by Zero.

I would suggest you trap for zero as Bernard has suggested with his

=IF(B1=0,"",A1/B1) formula.


Gord Dibben MS Excel MVP
 
Back
Top