This user has been fighting a losing battle, and the enemy isn't Excel, it's the assumption that exact math should solve an inherently imprecise problem. The spreadsheet demands a single perfect answer: exactly 1,800 calories, exactly 150g of protein, exactly 200g of carbs. But the real world of cooking doesn't work that way. You don't need 2kg of broccoli to hit a target. You need a system that understands constraints.
The matrix inversion approach is elegant, and it's exactly wrong. It finds the one mathematically correct intersection of three equations, but that intersection can land on absurd ingredient weights because the math has no concept of "this is too much broccoli" or "I can't eat 0.3g of chicken." The problem isn't the formula. It's the framework. The user has built a solver that treats every variable as equally flexible, when the real goal is to keep ingredient weights within realistic bounds and accept a small calorie variance as a feature, not a bug.
What this user needs is a shift in thinking: treat the calorie and macro targets as flexible ranges, not fixed points. Instead of solving for exact grams, set up a system that minimizes the distance between actual and target values while keeping each ingredient weight between, say, 50g and 500g. That is a constrained optimization problem, and while Excel's Solver is the obvious tool, the user correctly notes it won't auto-run on refresh. The workaround is to build a small iterative calculation using circular references or a goal-seek trigger via a macro. Or, more practically, use a lookup table that pre-calculates feasible combinations and picks one at random.
The core insight here is that AI-native spreadsheet tools can do what Excel 2021 cannot: accept ambiguity and return a practical answer. An AI model can understand that "close enough" is better than "exact but unusable." It can check a proposed solution against common-sense bounds before returning it. This user's struggle is a perfect example of where traditional spreadsheet logic falls short, not because the math is wrong, but because the problem was never purely mathematical. The next step isn't a better formula. It's a tool that knows when to let go of precision in favor of usefulness.