Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Calculation appears when it should be an error

    How can this be?

    The active cell is AA11. The formula is AB11 (an error) divided by R11 (a value).Clip0008.jpg

  2. #2
    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
    Are calculations set to manual? Otherwise could you attach a sample workbook so we could examine it?

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Kweaver

    Never mind the sample workbook. Your calcs are set to manual.
    No other way.

    zeddy

  4. #4
    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
    zeddy,
    I was only 99% certain (I could imagine no other way, but I have been surprised by things sometimes)...

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That was my first thought when I got the workbook, but it's not set to manual.
    There must be some corruption in this thing.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm attaching a very abbr version of the file. Don't worry about the other errors and circular references.

    I don't understand how the error can be in AB11 and the division work.
    Attached Files Attached Files

  7. #7
    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
    You have a circular reference, so even with auto calculations, they calculations are not being completed.

    Excel gets to the circular reference before completing that calculation and stops. If you go to File - options - formulas and check "enable iterative calculations" it will continue calculating even with the circular, but I would not trust the numbers completely unless you purposely built in the circular calculations to be iterative...

    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    kweaver (2013-09-03)

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Circular first, etc. If you're not careful, you learn something new every day. Thanks Steve.

    Kevin

  10. #9
    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
    I don't think it is a matter of circular first [See http://www.decisionmodels.com/calcsecretsc.htm for some comments on the calculation process], I see it is a matter of trying to recalculate all and choking on the circular since they can not be resolved...

    Steve

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Steve

    OK. You got me there. Circular calcs indeed!
    From the image posted, I didn't imagine that would be the cause - since a message would show if you had any circular calcs. But many persons get caught out with calcs being unintentionally set to manual if the first workbook they open in an Excel session happens to be set to manual mode, this dictates all further calc mode for subsequently opened workbooks.
    Now, to imagine another way that could give the same puzzling result, see cell [AA11] in attached file (I set it up so that there are no circular calcs.
    (OK, so I used Excel's camera tool. I knew you would find that cell [AB11] wasn't 'real'.)

    zeddy
    Attached Files Attached Files

  12. #11
    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
    Yes a problem with the image is that not enough of the screen is shown to see the "circular reference" remark. I knew as soon as I read the post, that indicated it had a circular reference that it was the problem and how to fix it. On occasion I have purposely created worksheets with iterative calculations and used this technique to iterate a result to find a "Steady state" solution.

    Steve

Posting Permissions

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