Results 1 to 15 of 16

20020820, 13:59 #1
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Recalculating Divide By Zero Errors (Excel 2000)
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.

20020820, 14:10 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
Hi,
Under ToolsOptions, 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.Regards,
Rory
Microsoft MVP  Excel

20020820, 14:32 #3
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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 cellbycell update. Very frustrating.

20020820, 14:39 #4
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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 ALTF11, 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)

20020820, 15:03 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
Are you using userdefined formulae or builtin 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.Regards,
Rory
Microsoft MVP  Excel

20020820, 15:16 #6
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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.

20020820, 15:24 #7
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
I have attached a nonspecific 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.

20020820, 15:45 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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

20020820, 15:48 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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?Regards,
Rory
Microsoft MVP  Excel

20020820, 15:55 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
copying in row 26
=IF(ISERROR(C23),0,IF(C23>C21,0,C21C23))
also should cure it
Steve

20020820, 17:00 #11
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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?

20020820, 17:34 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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.

20020820, 18:26 #13
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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.

20020820, 18:56 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
As I mentioned copying in row 26
=IF(ISERROR(C23),0,IF(C23>C21,0,C21C23))
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

20020820, 21:22 #15
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Recalculating Divide By Zero Errors (Excel 2000)
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 interestpaymentpreferred 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.John ... I float in liquid gardens
UTC 7ąDS