I’m running into the most confusing thing in my meal planning calculator and want to see if anyone has any ideas. Hopefully this is a good place to post this.

I’ve built a pretty thorough excel spreadsheet that helps me plan my meals and track my overall macros in each day of eating. It has a summary view with a macros/calories overview, one sheet per meal, and a master sheet of ingredients that I can pull into individual meals with a VLOOKUP. Neat, simple math throughout. Importantly, all of the ingredient calories and macros come from either the labels of the food items I’m using themselves, or the USDA or similar for anything without a label.

This is an example view of the macros/calorie overview. The top row is simple addition of all of the macros in my meals, and a sum of all of the calories. The middle row is the estimated calories per macronutrient, calculated by the USDA’s recommendation of 4cals/gram for carbs and protein and 9cals/gram for fats. Below that is an expression of the cals per macro as a percent of the total consumed calories, which SHOULD be my macro percentages exactly and should (in theory) add up to exactly 100%.

Perhaps you can already see the issue in the highlighted cells – the sum of the second row values is more calories than I am consuming in a day if I go by nutrition labels. And the percentages add up to more than 100%! I’m at a total loss, I could see if somehow the summed calories of each macro by gram was less than my total calories consumed, I’d call that a rounding error. But it being more by a significant amount is baffling me.

Anyone have an explanation for this weirdness? Do nutrition facts labels under-report calories? Am I making a math mistake somewhere? I’d love any thoughts or insight anyone has!