Getting Rid of #DIV/0! errors on a massive scale

Posted by Hansoh on December 03, 2001 7:20 AM

i've inherited some worksheets. the worksheets are crammed with formulas that link to external files. within the thousands of formulas, there are hundreds of #DIV/0! errors because of zero values in the denominator.

up until now, in cells where i want to show a value of zero rather than the #DIV error, i deal with it this way:

simplified original formula: =A1/B1
simplified new formula: =if(iserror(A1/B1),0,A1/B1).

in english, the 'if' formula says, 'if B1 is zero, pass zero; otherwise, do the math.' simple enough.

i'm not sure if this is the BEST way to deal with this, but i've been doing it up to now, and it's worked great.

first of all, is there a better way to deal with this? please advise.

and secondly (my main question), if i've inherited a file with thousands of these formulas without the #DIV if(iserror) check, how can i now go back and insert the if(iserror) into these thousands of formulas?

note 1: i can not just go into the first cell of the array and drag down and across because of the way the original formulas were written.

note 2: i can probably do a sophisticated find/replace in all of the formulas to insert the 'if(iserror)' condition, but it's going to be messy.

note 3: 1 way to solve this problem is to basically make a copy the array of those thousand formulas into new worksheet, and then do the if(iserror) formula ON the original array IN the new array. for example:

original worksheet cell AA100: =A1/B1
new sheet: if(iserror(SHEET1AA100),0,SHEET1AA100)

this will do the trick, but it's not elegant and unnecessarily doubles the size of the file.

please advise. thanks in advance.

han

Posted by Mark W. on December 03, 2001 7:51 AM

A better formulation...

ERR

Posted by Dank on December 03, 2001 8:17 AM

You could try this simple macro which 'wraps' the error trapping part of the formula around the current formula.

Regards,
Daniel.

Sub WrapFormula()
Dim rnge As Range, cl As Range, strFormula As String

Set rnge = Selection

For Each cl In rnge.Cells
strFormula = cl.Formula
If InStr(1, strFormula, "ISERROR") = 0 Then
strFormula = "=IF(ISERROR(" & Right(strFormula, Len(strFormula) - 1) & "),0," & _
Right(strFormula, Len(strFormula) - 1) & ")"
cl.Formula = strFormula
End If
Next
End Sub

Posted by Hansoh on December 03, 2001 8:37 AM

Re: A better formulation...

yes, if(b1,a1/b1,0) is a cleaner formula. i'll start using this. thanks.

han

Posted by Hansoh on December 03, 2001 8:46 AM

i was hoping that there was a solution for this problem before i resorted to VBA.

if i don't get any other VBA-less solutions, i will use your code. in looking at the code, it's clean and simple. some issues i'll have to deal with are: 1) if values in original external links change and new #DIV/0! errors show up, i have to re-run the macro. 2) and i have to actually SELECT the range each time i run the macro. 3.) if i want to do the ifiserror check only on a portion of the formula (i.e., NOT on the whole formula), i'll have to customize the code.

however, all in all, i like the code and i think i'll put it into my personal.xls so i can use this for other applications in the future. thanks again.

han