•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Solve equation system while allowing an error margin
Our take
Hello! It sounds like you're embarking on an exciting journey to create a nutrition spreadsheet that efficiently calculates ingredient amounts to meet your calorie and macronutrient targets. You're right in seeking a solution that allows for flexibility within your constraints. Instead of relying on exact solutions, consider implementing a formula that introduces a margin of error, enabling more realistic ingredient weights. This approach can help balance your recipe outcomes while ensuring they remain practical and achievable.
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- I need to adjust rows of values based on a percentage against fixed values on my recipe calculator.I’ve tried the Ai route and it’s helped, but I can’t quite nail this. I’m struggling with the formulas to execute. Excuse poor terminology, this is not my field of expertise. I’m writing a recipe calculator with the following columns- ingredients (C), grams (D), percentage of ingredient of total recipe (E), sugar % (F), fat % (G), protein % (H), carbs % (I), sodium % (J) and calories (K). The ingredients and nutritional % relating to each ingredient is populated via a dropdown list pulled from a table on another worksheet in the same work book. My goal is to have 20 rows of ingredients. 5 rows (rows 3-7) will have fixed weight (grams) values that don’t change as I make adjustments. The remaining 15 rows (rows 8-22), I would like to adjust the grams required in the 15 rows to achieve the % result I need of the nutritional value I determine. The bottom of the table will have a total % of each nutritional value that I want to be able to adjust. M For example- 1000g ice cream recipe requires 22% sugar Row 3 - 476g Cream = 2.6% sugar Row 4 - 204g Milk = 4.8% sugar Row 5 - 3g Vanilla Bean = 0% sugar Row 6 - 149g Egg Yolk = 0.2% sugar Row 8 - 125g Caster Sugar = 100% sugar Row 9 - 43g Glucose = 80% sugar This recipe = 31.27% sugar. I’d like to enter the % value at the bottom of the table, to change the weight of ingredients in rows 8-22. To further explain, in this example, i want the added sugar (caster sugar & glucose) to update their weights to calculate the final percentage as required. I would also like to do this with fat as well in the future. submitted by /u/occasional_superhero [link] [comments]
- Get rid of rounding errorHello, got a problem with a spreadsheet at work. For an calculation I need to calculate the average Full time equivalent of staff. Sheet is set up like this this: Column A: Person ID Column B: duration of period in days Column C: full-time equivalent Column D: weighted full-time equivalent for period Column E: average Full time equivalent over year Column A to C are preset base data. Formulas are in column D and E Calculation is basically done, broken down the formula is: In column D: =ROUND([duration days]/365×[Full time eqivalent for period];4) In column E it is: =SUMIF([Person ID]; [Person ID]; [Weighted for period]) But because if the rounding it doesn't add up to 1, it gies to 0,9999, even if the full year average is 1. Any ideas, how to fix that? (Other than IF(0,9999; 1; do the math) 😂) Was thinking about something like " if all Full time equivalent for period (column C) for one person ID (column A) are 1,0000, then make the average over year 1,0000, if not make the calculation based on my current formula for column E" but I can't get the operators right for that. Thanks in advance fir any help😂 Edit: moving rounding to column E instead of D did the trick. Sometimes its the obvious stuff xD Thanks to all submitted by /u/AttemptSlow612 [link] [comments]
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- Lookup in table, return a formulaIs there a way to lookup a name/value of a cell in a table and the resulting answer is a formula? I am wanting to build a calculation, which could use 1 or more variables, depending on the value of a dropdown menu (Im assuming a drop down, since that would force the exact name/format of the formula callout). https://preview.redd.it/bhib489h9fpg1.png?width=1115&format=png&auto=webp&s=c79605dbb3388ec2e3471e60aecb4dd1f304bcfd Above is a quick and dirty example. If in Cell8, there is a drop down that listed the values in column J (1 Face Area, Length Face Area, etc.). In Cells D-F8, a user can input the thickness, length & width. Cell H8 would lookup the value from C8, reference the table from J5:K11, and after finding the formula, it will do the appropriate calculation. What I listed as a formula is just text for the example. I am open to adjusting as needed. This is just an example. I know I could do a bunch of imbedded IF statements in this example, but assume the table from J5:K11, could be many many lines. The table could be as many columns and rows as needed. Further, the example is just a single line (row 8), the intent would be to have many (10's, 100's or even 1,000's) rows of data, so calculating the answers and looking those up the table wouldn't work, like a single line entry. Is this even possible? I would prefer to accomplish this without VBA, if possible. EDIT: Here is another version of the example. As you can see I am planning on having multiple lines of entries. Some will have the same dropdown formula requested (i.e. Length Face Area), while others may never be requested. Hopefully that clarifies a bit. Also, while I would prefer to do without VBA, if that is the "easiest" solution, I am not opposed to entertaining. https://preview.redd.it/kqil9xiljfpg1.png?width=759&format=png&auto=webp&s=71f6d68f69bd0f6a5550d256b344032d7bc9ccbb submitted by /u/ernstm63 [link] [comments]