Home |
Search |
Today's Posts |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide By 0
"Gord Dibben" <gorddibbATshawDOTca wrote...
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 .... 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 EditReplace 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide by zero | Excel Worksheet Functions | |||
Divide by 3 | Excel Discussion (Misc queries) | |||
divide by 0 | Excel Worksheet Functions | |||
Divide by Zero | Excel Worksheet Functions | |||
Divide one row over other row I dont wont to divide one number | Excel Discussion (Misc queries) |