Simplfying DIV/0 Error...Can you?

[Neil M]

I have a sheet with hudreds of cells with the similar formula as follows:

IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0))

The formula works okay unless J4 is blank then I get DIV/O Error in several
cells and it affects the last cell that totals numbers.

I have modified the formula to read as follows:

IF(ISERROR(IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW
EF",((F4/I4)),0)),0,IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0))

This also works, but it is very tiresome to have to change hundreds of cells
at this point.

QUESTION:
Is there an easier way to do this by formatting all the cells or the entire
sheet in any way?

THanks,
Neil M

 

[Bob Phillips]

=IF(OR(I4=0,J4=""),0,IF(J4="EF Vert",F4/I4,0)+IF(J4="EW EF",F4/I4,0))

 

[Spiky]

I have a sheet with hudreds of cells with the similar formula as follows:

IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0))

The formula works okay unless J4 is blank then I get DIV/O Error in several
cells and it affects the last cell that totals numbers.

I have modified the formula to read as follows:

IF(ISERROR(IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW
EF",((F4/I4)),0)),0,IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0))

This also works, but it is very tiresome to have to change hundreds of cells
at this point.

QUESTION:
Is there an easier way to do this by formatting all the cells or the entire
sheet in any way?

THanks,
Neil M

There should be a way to copy this quickly to all the cells at once
after fixing just one cell by hand. Is it a column of this formula?
And I'm guessing the formula changes in each different cell. How is it
set up?

 

[Gord Dibben]

You would be best off to re-build as Bob suggests but if you want to add the
ISERROR to existing formulas use this macro.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Note that ISERROR will mask all errors, not just the #DIV/0!


Gord Dibben MS Excel MVP

 

[Neil M]

It would not be hard to copy and paste, just tiresome. This is more for
future reference.
I think I will give Bob Phillips formula a try and see how that works.

As far as masking all errors, I am not worried about it for this sheet as
the blank cell is the only thing that would cause the error. ONce that is
filled everything else works.
Normally if it were just for me, I would leave it alone knowing that a value
in JF would eventually solve the problem. However this is for others in the
office to use later and I don't need a hundred emails telling me that the
sheet has an error before they fill in cells.

Thanks for all the help!!

Neil