Hiding #DIV/0!

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I have a division formula and when there is nothing in one
of the cells that is in the formula I get the #DIV/0! in
the cell. Is there a way to hide this unless the cells in
the formula have a value? Thanks.
 
Todd
One way: Say you want to divide A1 by B1.
=A1/B1 gives you the error you got if B1 is blank or zero. Change the
formula to:
=If(B1=0,"",A1/B1)
This gets you a blank cell if B1 is blank or zero. HTH Otto
 
Todd,

The basic concept is to test for zero before attempting the division.
e.g.

=A1/B1
will give you the error if B1 = 0

so the way around it would be:
=IF(B1=0,"",A1/B1)

John
 
Hi Todd,

Try using the formula below.

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

Hope this helps.

Regards,
James S
 
-----Original Message-----
I have a division formula and when there is nothing in one
of the cells that is in the formula I get the #DIV/0! in
the cell. Is there a way to hide this unless the cells in
the formula have a value? Thanks.
.
Click to expand...
Todd:

I just went through the same type of thing. Essentially, I
start the formula in the division cell with an IF
statement. Here's an example:

In cell where division is to take place:
=IF(B4>0,(M4/G4),(0))

Translated, it says if B4 is greater than zero, then
divide M4 by G4; if not, enter a zero. Under the Tools,
Options, View tab, be sure the display zeroes check is off
and your cell will remain blank.

Unfortunately, you have to do this for every cell you
don't want the #DIV/0! to display.

FYI ... I also use IF statements to suppress goofy "FALSE"
displays, too, or from repeating the last totals of
columns all the way down that have additional rows to
add information later (I more often than not want those
cells to be blank rather than carry down the whole sheet).

If anybody out there has a better way, please let me know.
 
Back
Top