Formulas Divided by 0 =#Div/0!

  • Thread starter Thread starter Chad Cameron
  • Start date Start date
C

Chad Cameron

I usually come across this problem, and I fix it with a simple if statement.

But today, I have received a huge spreadsheet and this problem is all over
the place, at least 100 entries. Cut and paste the modifed formula with an
if statement will not work.

Is there an easier way to show a 0 or blank in this cells instead of #Div/0!

Thanks
Chad
 
If you're only interested in hiding the errors--not changing the values, you
could use conditional formatting.

Select the cell (say A1)
and then
Format|conditional formatting (xl2003 menus)
formula is:
=ISERROR(A1)

And make the font color the same as the fill (white on white???).

This won't remove the error -- it just hides it.
 
Thanks Dave,

Looking deeper into it, I cannot have a blank because then people might type
stuff in there. I don't want to have to lockdown the spreadsheet. We use
Green Font to show that it is a formula and should not be touched. So, a
blank will not work, therefore neither will the conditional formating.

If I need a '0' in the cell, is there another way other than the if
statement? The more I think about it, the more I realize that the #Div/0!
is there for a reason, and is probably not easy to get rid of. I guess I
could use .0001 instead of 0.

Thanks
Chad
 
Just use conditional formatting to return a green "0". People will know it
is a formula.


Chad Cameron said:
Thanks Dave,

Looking deeper into it, I cannot have a blank because then people might
type stuff in there. I don't want to have to lockdown the spreadsheet.
We use Green Font to show that it is a formula and should not be touched.
So, a blank will not work, therefore neither will the conditional
formating.

If I need a '0' in the cell, is there another way other than the if
statement? The more I think about it, the more I realize that the #Div/0!
is there for a reason, and is probably not easy to get rid of. I guess I
could use .0001 instead of 0.

Thanks
Chad
Click to expand...
 
=if(iserror(yourformula),0,yourformula)
or
=iferror()
Only in xl2007

are the only ways I know to do it.
 
Thanks AAM & Dave,

It appears I will have to format every cell (Thanks AAM).

Chad


Chad Cameron said:
Thanks Dave,

Looking deeper into it, I cannot have a blank because then people might
type stuff in there. I don't want to have to lockdown the spreadsheet.
We use Green Font to show that it is a formula and should not be touched.
So, a blank will not work, therefore neither will the conditional
formating.

If I need a '0' in the cell, is there another way other than the if
statement? The more I think about it, the more I realize that the #Div/0!
is there for a reason, and is probably not easy to get rid of. I guess I
could use .0001 instead of 0.

Thanks
Chad
Click to expand...
 
Post back how you used conditional formatting to return a green 0.

Chad said:
Thanks AAM & Dave,

It appears I will have to format every cell (Thanks AAM).

Chad
Click to expand...
 
You are a step a head of me.

I can't.
I give up. I will just use the if statment to check for dividing by 0.
It's a bit more work, but I know I get the answer I want.

Thanks anyways,
Chad

Dave, your ISERROR function would have worked, but I wanted a 0 visible.
 
Why wasn't it visible?

=if(iserror(yourformula),0,yourformula)

If you had used:
=if(iserror(yourformula),"",yourformula)
I'd understand.
 
Sorry, you are right, I was thinking of just using the IsError with the
conditional formatting., not putting it in an if statement. If I was going
to use an if statement, then I was just going to check if the denominator
was 0, and not look for the error.

Chad
 
If you know both cells are going to be numeric, then checking to see if the
denominator is non-zero makes perfectly good sense.

If you're not sure it the user is typing numbers, then you may want to stop
other errors.

But you may want to see those #value! errors--it'll give a hint to the user that
they typed text.

Chad said:
Sorry, you are right, I was thinking of just using the IsError with the
conditional formatting., not putting it in an if statement. If I was going
to use an if statement, then I was just going to check if the denominator
was 0, and not look for the error.

Chad
Click to expand...
 
Try following codes
Code: 
Private Sub mySub()
Dim row As Integer, col As Integer
For row = 1 To 100
For col = 1 To 10
If IsError(Sheet1.Cells(row, col).Value) Then
Sheet1.Cells(row, col).Value = ""
End If
Next
Next
End Sub



Chris
 
Back
Top