Is it possible to treat and display a #div/0 error as a zero?

A

Andrew

I have an output worksheet that relies partially on the the results of
formulas from linked worksheets. I want to be able provide users the option
of not inputing into every other linked worksheet, and thus treating any
#div/0 error as a simple zero value instead. Is there any way to do this
tools, etc?
 
R

Ron Rosenfeld

I have an output worksheet that relies partially on the the results of
formulas from linked worksheets. I want to be able provide users the option
of not inputing into every other linked worksheet, and thus treating any
#div/0 error as a simple zero value instead. Is there any way to do this
tools, etc?
Click to expand...



To change div/0 errors to 0, but leave other errors unchanged, you could use:

=IF(ISERROR(your_formula),IF(ERROR.TYPE(your_formula)=2,0,your_formula),your_formula)

If you have XL2007:

=IFERROR(your_formula,IF(ERROR.TYPE(your_formula)=2,0,your_formula))

--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to get rid of #DIV/0! 2
Using ISERROR to Solve #DIV/0 in a formula 13
not wanting #div/0 to chart as zero 1
#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ? 3
Find #DIV/0! Error and Replace 6
#div/0 errors and blank cells 7
remove error value when formula exists for empty cells 2
COUNTIF: everything in a row larger than zero over multiple worksh 1

Top