#DIV/0!.......how to get rid of it

R

renold1958

Have a spreadsheet....one column keeps coming up with #DIV/0!.........I know
reasons as of to why, etc etc............is there anyway have the #DIV/0!
values disappear from a column?
 
M

Mangus Pyke

Have a spreadsheet....one column keeps coming up with #DIV/0!.........I know
reasons as of to why, etc etc............is there anyway have the #DIV/0!
values disappear from a column?
Click to expand...

If column A has a price and column B has a weight, and you're dividing
these to get the price per pound in column C:

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

Replace something with whatever you want displayed in lieu of the
division by zero error. So if you want it to be blank, put: ""

MP-
 
S

Sandy Mann

if the reason "as of to why, etc etc....." is the fact that - say cell C5 -
is blank the try:

=IF(C5=0,"",B5/C5)

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
S

Sandy Mann

Woops should have been:

=IF(C5="","",B5/C5) unless C5 could contain a zero when
=IF(OR(C5="",C5=0),"",B5/C5) or =IF(SUM(C5)=0,"",B5/C5) will be needed.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
B

Bond S.C

Hi!
You can use it

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


Bond! <Chon, Sung-Chul>


"renold1958"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:
 
B

Bob Umlas

If there are a lot of formulas here, you can avoid recoding them to the
suggested formulas by simply selecting them all, use Format/Conditional
Format, and, using the active cell's address, say C1, change "Cell Value
is", to "Formula Is", then enter =ISERROR(C1), click Format button, click
the Font Tab if necessary, and choose a white font (or whichever one matches
the background), then OK your way out. All errors won't show.
 
A

abcd

... unless you try to print them: often, a "non visible" color may be
auto-changed by ecxel or the printer sometimes and then printed.

So, the better solution stay to keep it empty, but if used only on the
screen or if the pair background-filling and font-color are keeped by
the printer it may be ok to use your idea.
 
Top