Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2016
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel Making Large Addition Errors

    Hi, folks. Feel like I'm going crazy. Excel is making large addition errors when I delete cells that I don't think can be explained by float/rounding.

    The attached screenshots are from a nutrition plan (plan itself also attached). Column E shows the calories for each food and E2 is a SUM() of the column and it correctly adds up to 2961.75. However, if I remove the flax and chia seeds in rows 10 and 11 (138+96 = 234 calories) the total should be 2727.75. But as you can see it is not, I'm getting a total of 2493.75, which is wrong. I do observe that this is off by precisely 234, so it looks like Excel is over-correcting by double subtracting the deleted values. Does anyone have any idea what is causing this and how to fix it?

    A few things I'll note or have observed:
    1. This happens ONLY for rows 10 and 11. It doesn't happen for any other entry.
    2. This happens whether I delete just the calorie values or all the values.
    3. These are calculated values from another tab (as are almost all the items on this tab).But the error remains even if I change those cells to simple numbers (just the values)
    4. If I make meal-by-meal subtotals (shown at the bottom right of each meal) the substotals are correct. BUT if I add them up I will get the same incorrect total (i.e., it treats the 503.25 as if it were 269.25).
    5. If I construct a sum that skips those cells with leaving them ntouched (=SUM(E6:E9,E14:E16,E19:E25,E28:E29)) I get the correct total, but if I zero those cells the incorrect total reappears! As if Excel know I'm skipping the "voodoo" cells.
    6. I do not have the sample problem if I copy paste the values to a new cell and add them, so it isn't something about the values themselves.

    I know in this case that it would be trivial to solve this by recopying the values into a new spreadsheet, but now I'd like to know the cause so that I don't have to worry that other spreadsheets are doing the same thing without my catching them.

    Thanks for any ideas you all have.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Baffle,

    It's not Excel it's the Excel User!

    In row 19 & 20 you Reference back to Rows 10 & 11 of the Rest Day sheet rather than back to the Foods sheet.

    Thus when you zero out rows 10 & 11 you also zero out rows 19 & 20 thus doubling the effect.

    Replace the formulas in Rows 19 & 20 with references back to the Foods sheet and things work as they should.

    Bad User.JPG

    Note: I hid rows 12-16 to reduce the size of the screen capture!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    baffle (2016-08-17)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi baffle

    ..on sheet [Rest day], when you delete your breakfast chia in cell [E10], this will make the Shake chia value in cell [E19] a zero as well!!!
    ..same thing with your flax in cells [E11] and [E20]
    So, if you don't want your Chia and Flax for breakfast BUT you still want your Shake chia and flax, you must remove the formulas in cells [E19] and [E20]

    ..problem solved???

    ..I'm having a Wagu steak

    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    baffle (2016-08-17)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    ..the timings of our posts are the same.
    Does this mean that zzzz's are always last in order?

    a zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    baffle (2016-08-17)

  8. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Just like in the Olympics! One one hundredth of a second! I get the Gold you'll have to settle for Silver!

    Is that the Star Spangled Banner I hear playing in the background?

    ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    ..by my calculations, you are either still awake from the night before or you should still be in bed at this time.
    GET SOME REST!!!
    ..and yes, you deserve the Gold.

    aaaaazedddy

  10. #7
    New Lounger
    Join Date
    Aug 2016
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Of course! Well gosh that was dumb of me....

    I figured such a large mistake should have been PEBKAC but I guess I just needed another pair of eyes to catch it. Thanks so much, RG and Zeddy. Really appreciate it.

Posting Permissions

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