1. ## 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.

2. 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.

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

HTH

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

baffle (2016-08-17)

4. 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. 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. 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

9. 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. 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
•