Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
renold1958
 
Posts: n/a
Default #DIV/0!.......how to get rid of it

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?


  #2   Report Post  
Mangus Pyke
 
Posts: n/a
Default

On Sun, 17 Jul 2005 06:39:01 -0700, "renold1958"
wrote:
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?


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-

--
"Learning is a behavior that results from consequences."
B.F. Skinner
  #4   Report Post  
Sandy Mann
 
Posts: n/a
Default

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

Replace@mailinator with @tiscali.co.uk
"renold1958" wrote in message
...
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?




  #5   Report Post  
Bond S.C
 
Posts: n/a
Default

Hi!
You can use it

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


Bond! <Chon, Sung-Chul


"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?




  #6   Report Post  
renold1958
 
Posts: n/a
Default

Thank you all for your fast replies........much appreciated

"Mangus Pyke" wrote:

On Sun, 17 Jul 2005 06:39:01 -0700, "renold1958"
wrote:
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?


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-

--
"Learning is a behavior that results from consequences."
B.F. Skinner

  #7   Report Post  
Bob Umlas
 
Posts: n/a
Default

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.

"renold1958" wrote in message
...
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?




  #8   Report Post  
abcd
 
Posts: n/a
Default

... 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to sum cells and ignore the #div/0! 's ? Todd Excel Worksheet Functions 6 April 25th 10 12:31 AM
How do i make a sum formula ignore #div/0! errors in the range shat Excel Worksheet Functions 6 April 22nd 06 02:47 PM
replace "#DIV/0!" error with blanks Mark B Excel Worksheet Functions 0 June 22nd 05 10:19 AM
# DIV/0! error in Excel Helpwanted Excel Discussion (Misc queries) 8 May 6th 05 09:31 PM
blank instead of DIV/0! bill gras Excel Discussion (Misc queries) 2 March 21st 05 02:43 PM


All times are GMT +1. The time now is 01:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"