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

    F9 shows different answer than cell (2002)

    I've dragged over a complex formula that shows in the formula bar and touching F9 show a result of 350; yet, the number that appears in the cell is 400.00.
    How can that be? Checking the format, it's a "number" with 2 decimal places and ,'s in thousands.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: F9 shows different answer than cell (2002)

    Can you post a (trimmed down & censored, if necessary) copy of the workbook?
    Gre

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

    Re: F9 shows different answer than cell (2002)

    Unfortunately, not easily as there are so many inter-connections between cells and sheets.
    What would I begin to look for that might suggest that 400.00 would appear versus the 350 that shows when I do an F9?

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: F9 shows different answer than cell (2002)

    It doesn't necessarily follow from what you've said so far, but it would sound as if you're working with rounded figures. If so - at a very rough guess - you may be inadvertently rounding to the nearest hundred , rather than to the nearest ten. HTH
    Gre

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

    Re: F9 shows different answer than cell (2002)

    That was my first thought, but none of the other cells with the same formula (using other data) aren't rounded. And, there's no formatting indication that it is rounded.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: F9 shows different answer than cell (2002)

    Is Iteration (Tools | Options | Calculation) turned on for this spreadsheet?
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: F9 shows different answer than cell (2002)

    What exactly does the formula try to do?

    When I was working on someone else's worksheet once, I was getting wrong answers from my formulas. It turned out to be due to the fact that I didn't have iterations checked under Tools>Options on the Calculation tab. They had set up some circular references and this needed to be checked for the numbers to calculate correctly...could this possibly be what's wrong?

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

    Re: F9 shows different answer than cell (2002)

    Stupid me! I just realized my problem. I should have been using an array formula and didn't. Weird how the answer was off by $50. A simplification of the calculation in the cell: I add respective cells of several columns of data (e.g., J2:J100+L2:L100+P2:P100) and forgot to make this array calc an array formula. SIGH. Thanks to those of you that tried to help in vain. Phew. At least I got over that hump.

Posting Permissions

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