How to hide "#DIV/0!" error...

[ryan987]

Building a spreadsheet that has daily updates.....

I have it doing all the calculations i want - and even some great cell coloring via 'conditional formatting' - thanks to the help i've found on this site.

Since i have daily updates, i've copied the formulas from several columns down.... so needless to say, since there are no numbers to calculate, i have a spreadsheet full of "#DIV/0!"....

How do i 'hide' that error and have it magically disappear when i enter the numbers for the day?

 

[Ed in Aus]

have a little look at this... http://office.microsoft.com/en-au/excel/HP052091471033.aspx

should be able to do an if statement to show blank if there is an error

 

[ge0rge]

It helps to post the formula with which gives you this error. I may be able to help as I have had this happen a few times last week and figured it out.

 

[jbeaucaire]

Best way to "hide" the error is to not let it occur. If this formula:

=A1/B1

..caused an error because B1 was blank, then this version makes sure there is a value there before attempting that calc:

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

 

[Jeshua]

I use iserror and it works pretty well for me


=if(iserror(A1/B1),0,A1/B1

 

[ryan987]

It helps to post the formula with which gives you this error. I may be able to help as I have had this happen a few times last week and figured it out.

ok, cells B,C, D, E are the ones that get updated daily.... Cell F and G's formulas are:
F= E10/B10 and G=Sum(F5:F10)/5 .....there's more but if i could just get one or 2 fixed, i'll be able to figure out the rest of the columns.

thanks again for everyone's help!!!

 

[jbeaucaire]

The formula suggestions in post #4 and #5 directly relate to your sample. Use one of them.

 

[ryan987]

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

That worked!!! the other formula left a '0'

Thanks so much!!!