Formulas Divided by 0 =#Div/0!

[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

 

[Dave Peterson]

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.

 

[Chad Cameron]

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

 

[AAM]

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

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

 

[Dave Peterson]

=if(iserror(yourformula),0,yourformula)
or
=iferror()
Only in xl2007

are the only ways I know to do it.

 

[Chad Cameron]

Thanks AAM & Dave,

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

Chad

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

 

[Dave Peterson]

Post back how you used conditional formatting to return a green 0.

Thanks AAM & Dave,

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

Chad

 

[Chad Cameron]

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.

 

[Dave Peterson]

Why wasn't it visible?

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

If you had used:
=if(iserror(yourformula),"",yourformula)
I'd understand.

 

[Chad Cameron]

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

 

[Dave Peterson]

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.

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

 

[chris]

Try following codes

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