Divide By 0

  • Thread starter Thread starter Mike Short
  • Start date Start date
M

Mike Short

The workbook I inherited contains many cells that are
vulnerable to divide by zero errors. Is there any "global"
fix so if the divisor is zero the error will not be
displayed?

Thank you
 
Mike,

There is no such setting. You would have to modify the formulas
individually.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Mike

If you are willing to use a 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 & "),0," & myStr & ")"
End If
End If
Next
End Sub

Select the cells and run macro.

Gord Dibben XL2002
 
Mike,

In addition to the other two answers so far:

If you just want that "the error will not be displayed", you can use Conditional Formatting with a formula like =ISERROR(A1) and format for white text if the condition is true. The error will still be there, but it will not show.

Regards
Anders Silven
 
Gord Dibben said:
Mike

If you are willing to use a 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 & "),0," & myStr & ")"
End If
End If
Next
End Sub
Click to expand...
....

Wholesale error trapping is almost always a bad thing in only one sort of
error needs to be trapped. This is especially true for #DIV/0!

The ideal way to trap #DIV/0! in formulas involving division is

=IF(Denominator,Numerator/Denominator,SomethingElse)

Note that it may often be the case the SomethingElse should be "" or some
explanatory string rather than numeric 0. Granted Edit>Replace could be used
after running the macro above to replace ,0, with ,SomethingElse, as long as
the macro-added ,0, were the only instances of this term.

The ideal way to trap #DIV/0! in AVERAGE calls involving no numeric entries
is

=IF(COUNT(Sample),AVERAGE(Sample),SomethingElse)

The ideal way to trap #N/A in MATCH and [V|H]LOOKUP calls is

=IF(ISNUMBER(MATCH(Value,INDEX(Table,0,1),0)),
VLOOKUP(Value,Table,ColumnIndex,0),SomethingElse)

The ideal way to trap #VALUE! in FIND and SEARCH calls is

=IF(FIND(fs,ss&fs,sp)<=LEN(ss)-LEN(fs)+1,FIND(fs,ss,sp),SomethingElse)

The OP's workbook is inherited, so ipso facto multiple user and subject to
maintenance by someone other than the original author. In that situation,
trapping #DIV/0! using ISERROR is extremely short-sighted. If all it trapped
were #DIV/0!, no big deal (unless the numerator or denominator evaluate
separately to #DIV/0!). But ISERROR would trap all the other errors, thus
eliminating potentially useful diagnostic information. Never trap all error
values when it's relatively simple to trap the one or two error values that
should be trapped, and never use error trapping if there's some other way to
detect conditions that produce error values.

Finally, quibbles: when is Right(Cel.Formula, Len(Cel.Formula) - 1) ever as
efficient as Mid(Cel.Formula, 2) ? Also, no benefit would be gained from
inserting 'IF(ISERROR(' before a formula starting with 'IF(ISERR(', so the
condition could usefully be changed to

If Not Cel.Formula Like "=IF(ISERR*" Then
 
Back
Top