Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    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.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Recalculating Divide By Zero Errors (Excel 2000)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    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 cell-by-cell update. Very frustrating.

  4. #4
    Silver Lounger
    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 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)

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Recalculating Divide By Zero Errors (Excel 2000)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    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.

  7. #7
    2 Star Lounger
    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 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.
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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,C21-C23))

    also should cure it

    Steve

  11. #11
    2 Star Lounger
    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?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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.

  13. #13
    2 Star Lounger
    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.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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,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

  15. #15
    Uranium Lounger
    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 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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •