Error Divide By Zero

[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

 

[jim]

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

 

[Bernard Liengme]

Assuming you need the formula =A1/B1
Replace it by =IF(ISERROR(A1/B1),"",A1/B1)
or =IF(B1=0,"",A1/B1)
best wishes

 

[jim]

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.

 

[diablo]

Wow, that was fast, that was exactly what I needed. Thank you.

Brian

--

 

[diablo]

Exactly what I needed, thanks Bernard.

Best regards,
Brian

--

 

[Gord Dibben]

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