getting rid of #DIC/0!

J

James

is there something you can add to a formula so if it equals #DIV/0! the cell
will remain empty or blank?
 
T

Tom Hewitt

Option I use, which might not be the best one, but works is


=IF(ISERROR("Put your formula here")=TRUE,0,"Your Formuala Here")

Bascially if your formula returns an error just as DIV or NA etc it returns 0

Hopes this helps

Tom
 
H

Harald Staff

=IF(B1=0;"";A1/B1)
or -excel 12 only:
=IFERROR(A1/B1,"")


HTH. Best wishes Harald
 
R

rewb2

is there something you can add to a formula so if it equals #DIV/0! the cell
will remain empty or blank?
Click to expand...

You need an error trap, and there are several ways to do this. I'm
assuming your formula is =A1/B1. You'll need to adjust the following
formulae to refer to the cells that form the actual numerator and
denominator to your formula.

The most robust #DIV/0! error trap, in my opinion, is simply:

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

which returns a zero rather than #DIV/0! error. You could use:

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

if you want the cell to remain blank.

There are other ways to do it, such as using =IF(ISERROR(A1/B1),"",A1/
B1) or =IFERROR(A1/B1,"",A1/B1) if in Excel 2007.
 
J

James

thank you so much you just cleaned up my data

[email protected] said:
You need an error trap, and there are several ways to do this. I'm
assuming your formula is =A1/B1. You'll need to adjust the following
formulae to refer to the cells that form the actual numerator and
denominator to your formula.

The most robust #DIV/0! error trap, in my opinion, is simply:

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

which returns a zero rather than #DIV/0! error. You could use:

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

if you want the cell to remain blank.

There are other ways to do it, such as using =IF(ISERROR(A1/B1),"",A1/
B1) or =IFERROR(A1/B1,"",A1/B1) if in Excel 2007.
Click to expand...
 
Top