Recalculating Divide By Zero Errors (Excel 2000)

Topic

New Reply

I’m new here, want to say hello to everyone. My problem is this: is there any quick and easy way to recalculate cells that show a Divide By Zero error if the error has been corrected? For example, if an assumption cell that is a precedent cell to many formulas gets changed, that change sometimes causes a Divide By Zero error. If I change the assumption cell back to the original value, the dependent cells still display the Divide By Zero error. I then need to visit each cell independently, press F2 to activate the cell and then hit Return to cause the error to disappear and be replaced by a value. I recognize that trapping for such an error is the best way, but that would not be feasible in this situation. Thanks in advance for any suggestions.

Reply | Quote

Replies

Hi,
Under Tools-Options, on the Calculation tab, is the Manual box checked? If you set it to automatic, Excel should automatically recalculate the formulae for you.
Hope that helps.

Reply | Quote

Yes, Calculations are set to Automatic. That’s what makes this such a pain. Even when calcs are on auto, you still have to visit each cell to update the error. In fact, if I inadvertently change the assumption cell, thereby causing the Divide By Zero error, even pressing CTRL + Z doesn’t clear the errors. I *still* have to do a cell-by-cell update. Very frustrating.

Reply | Quote

Are you using user-defined formulae or built-in Excel ones?
Can you post a workbook (suitable censored for any confidential info) that demonstrates the problem?
One further possibility – are you using natural language formulae? (i.e. using labels in formulae) If so, this is a known bug – see Q200688 for details.
Hope that helps.

Reply | Quote

I have attached a non-specific example of the problem. If you got to C9 and delete the contents, you will notice all the Divide By Zero errors that appear. If you then do a CTRL+Z, you will note that the Divide By Zero errors do not disappear. If you go then to cell C27, press F2 and then Return, you will see the errors for that cell and its dependent cells be replaced by values.

Reply | Quote

The problem is that you have circular references
Interest uses the ending balance
The ending balance uses begin balance and prin amort
the princ amort uses the interest and cash available

The problem should go away if you fix your worksheet to remove the circ references. Once it gets the error it can NOT be fixed through iteration

Steve

Reply | Quote

As far as I can see, it’s because you’re using iteration to get around the circular references you have. The only way around it I can see immediately is to change the formula in row 23 to something like:
=IF(ISERROR(AVERAGE(C25,C27)*($C$6)),0,AVERAGE(C25,C27)*($C$6))
or change row 21 to something like:
=IF(C20,C18/C20,0)
which seems to cure it.
Does that help in your situation?

Reply | Quote

copying in row 26
=IF(ISERROR(C23),0,IF(C23>C21,0,C21-C23))

also should cure it

Steve

Reply | Quote

So do you think that if the sheet had no circular references this phenomenon of the Divide By Zero errors not recalcing would not occur?

Reply | Quote

That is what I believe.
The circular errors are not displayed because the spreadsheet is iterating. Once the “error” is obtained, it can no longer iterate so it stops.

Reply | Quote

The problem, though, is that this is a debt model and there are always circular refs in such a model, so nothing can be done about taking them out. That seems to mean that if a particular user changes an assumption cell so that he/she gets the dreaded Divide By Zero error, there is no way to recalc that error outside of visiting the affected cells individually. It looks like I will need to create a macro that searches for Divide By Zero errors and recalcs them when the macro is executed. Can’t seem to find a better way.

Reply | Quote

As I mentioned copying in row 26
=IF(ISERROR(C23),0,IF(C23>C21,0,C21-C23))
should cure it.

Div by 0 errors are NOT eliminated, it just uses “0” as the principal amortization when the interest has an error. This should not be a big deal, since 0 is used when the Interest is > than the “Cash Available for Debt Service”. It just prevents the iteration from resetting after the error is eliminated.

Steve

Reply | Quote

If I understand correctly the iterative nature of the problem is that principal reduction is made from cash flow after interest payment, which depends on average principal after principal reduction. So an iterative model is appropriate for this problem.

If you really want to get out of an iterative model, you could use an interest-payment-preferred model similar to the one I have attached. Yellow highlighting shows the substantive changes I made, light blue shading shows formula changes others have suggested to reduce #DIV/0! errors. I also streamlined some formulas (force of habit).
[Model edited]

Review carefully to ensure that I understood what your model is attempting to do.

Reply | Quote

Thank you, John (and all the others who so kindly replied to my post). I will see if your suggestion is feasible. It looks good to me, but I am far from the final say on this issue.

Thanks again.

Reply | Quote

Have you considered creating a function in VBA. The custom function would perform the division and if it encountered or returned the error it would be replaced by a zero. The example code should be placed in a module. To create a module, press ALT-F11, select/highlight the VBA project relating to your file name, right click and select insert module…copy code into the module you just created.

Example:
Function TestError(Amount)
Application.Volatile ‘Forces the calculation now
If IsError(Amount) = True Then ‘Tests whether or not the Amount (cells being divided) is returning an error
TestError = 0 ‘If an error is returned replace the =TestError portion of the function with a zero
Else
TestError = Amount ‘If no error exits, just perform the division. TestError is now the result of the division
End If
End Function

You would have to include this function as part of your formula such as: =TestError(A1/A2)

Reply | Quote

I did consider that as an approach, but this workbook has been distributed to a number of others and it would probably not be possible to bring them all up to date. In fact, that’s part of the overall scope of the problem: other users who understand the native Excel formulas now in the workbook would have considerably more difficulty in understanding custom functions or formulas that trap for the error.

Reply | Quote