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.