#Div/0!

M

myssieh

Good afternoon...

I am running Office 2007 on Windows 2007. Is there a way to keep #Div/0!
from showing up. The cells are currently blank but will be populated
eventually. We want as much automation as possible by going ahead and
loading the formulas but it looks unprofessional for our customer to see this.

Thanks in advance for you help, I really appreciate it...
Myssie
 
T

T. Valko

You would need to post your formula for a specific suggestion but as a
generic suggestion try something like this:

=IFERROR(your_formula,"")

Note that this will only work in Excel 2007. For something that's compatible
with all versions of Excel:

=IF(ISERROR(your_formula),"",your_formula)

#DIV/0! errors are caused by trying to divide something by 0. You can also
just test that a certain cell is not empty/contains 0. For example, you have
a formula like:

=A1/B1

If B1 is empty/contains 0 you'll get the error. You can test B1 to trap the
error:

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

JLatham

In the cells giving you the error, you can "wrap" the formula in a test to
keep the error from being displayed.

Lets say your formula is now =F5/D9
and because D9 has a zero in it (or nothing) you get the error. You can
rewrite the formula like this
=IF(ISERR(F5/D9),0,F5/D9)
which would display a zero instead of DIV#/0 in the cell when D9 causes a
problem.
Or, you can write it as:
=IF(ISERR(F5/D9),"",F5/D9)
which would simply display an empty cell when D9 is zero or empty.
 
G

Gord Dibben

Windows 2007?

Back to the problem..........

Trap for divide by zero error.

=IF(A1="","",B1/A1) entered in C1

will leave C1 blank if A1 empty and B1 has a value but leave C1 with a zero if
A1 has a value and B1 is blank.

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

will leave C1 blank if either A1 or B1 is empty.


Gord Dibben MS Excel MVP
 
Top