Solve equation system while allowing an error margin
Hello. To preface, I am a complete beginner, and am using Excel 2021 in French. I've been fighting a losing battle against a spreadsheet for a few days.
In a nutshell, I'm trying to create a nutrition spreadsheet to calculate the amount of each ingredient I have to use in my meal to reach my calories and macronutrients targets. This will be linked to an ingredient randomizer, which will generate a 3-ingredient recipe each time the spreadsheet is refreshed.
I've tried to solve the equation system using matrices. The resulting (translated) formula would be =MMULT(MINVERSE(C3:E5);I6:I8), where C3:E5 (green) is a square matrix with the ingredients' nutritional value and I6:I8 (yellow) is the target weight of each macronutrient.
The problem, as seen in the example above, is that I end up with barely any chicken/rice and 2kg of broccoli. My formula gives me the only exact solution, whereas I need an approximate solution that stays within practical boundaries.
I'm sure there is a formula to apply upper and lower limits to a result (which I have yet to research), but I'm afraid that wouldn't entirely solve the problem since it would probably end up creating impossible equations.
Using the solver is also out of the question, since I need a formula that will automatically calculate each time I open/refresh the spreadsheet.
I'm trying to figure out a way to add some leeway while entering the initial calorie target, potentially offsetting total calorie count by ~50kcals as needed to provide realistic ingredients weights.
Any help or advice would be much appreciated, please let me know I if can provide more info/clarification.
Thanks in advance
[link] [comments]
Want to read more?
Check out the full article on the original site