Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide By 0
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide By 0
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 Short" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide By 0
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 On Sat, 29 Nov 2003 17:28:42 -0600, "Chip Pearson" wrote: 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 Short" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide By 0
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 "Mike Short" skrev i meddelandet ... 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 |
#5
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 |
Reply |
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) |