Hi,
I have a problem from relating to the optimal mix of foods to meet a target nutrient content.
Attached is a example sheet, the left hand side is a list of random foods and their nutritional content for 1 serving, to the right in yellow is the target nutritional content.
The question i have to answer is, what is the best mix of those foods to deliver the target nutritional content.
I thought solver would do this, but i couldnt work it out.
Does anyone have any ideas?
Thanks
Almonds (30g) H2: =$G2*B2 Tuna (1 Can) Veg Bag Basa (2 Pieces) Egg Whites (100ml) Peanut Butter (1Tbsp) Brown Rice (125g) Chicken Breast (100g) H10: =SUM(H2:H9) K12: {=SQRT(AVERAGE((H10:K10 - H11:K11)^2))}
Set K2 to Min by changing G2:G9
Entia non sunt multiplicanda sine necessitate
Using the sumproduct function you could set up a solver mode like this perhaps. You could also reach the target value without all the components. Just remove the constraint
that B2:B9 values should be >= 1
Alf
BTW, there are many (infinite) solutions; you have four equations (nutrients) in eight unknowns (servings).
Hi,Originally Posted by shg
Thanks for that, what if there was the added constraint of only whole servings can be used, and that not all foods have to be used?
Would you mind posting a workbook?
Thankyou
Hi,
I dont understand how you use the workbook, please can you elaborate?
Also what if there was the added constraint of only whole servings can be used, and that not all foods have to be used?
Thanks
Modified for portions being integers.
Some comments in file but + an image of solver setup. But the best way to view solver setup is to open the solver model.
Alf
Hi,
I just tried to replicated your setup myself, but it is giving me weird answers!!
even though i have set things to int i get weird answers to about 5 decimal places.
Have I missed something?
Thanks in advance, this is exactly the solution I was looking for
Thanks
Not sure of your excel version. You say excel 2003 but uploaded file had extension xlsx and this means excel 2007 or higher.
Strangely enough the default setting for excel 2010 is to ignore "Integer settings" so you could check the settings for solver in "Options". If box marked "Ignore integer setting is ticked, untick and test again.
If this don't solve your problem upload the problem file and I'll have a look at it. Be sure to specify your excel version as this has an influence on how the model should be set up.
Alf
Duplicate message courtesy of Swedish rails, deleted.
Missad that you already had uploaded a file. As my tab' s excel version don't include solver I'll have a look at the file when I arrive at my destination later today.
Alf
Last edited by Alf; 10-12-2018 at 02:17 AM.
You have set the constraint wrong i.e. "$I$4:$L$<=$I$3:$L$3" where it should be "$I$4:$L$>=$I$3:$L$3" as per instruction the instruction you had written in note 3.
There is also the possibility of limited the number of sevings of a specific kind. Running solver with the correct constraint according to not 3 will giv you this result
free_s.jpg
and adding an extra constraint say that range B3:B14 should be less or equal to 3 (i.e. B3:B14=<3) will give you this result
bound_s.jpg
but this gives you a higher total nutrient value than the previous solution. Perhaps the best approach would be to add the cost of each serving and solve for minimum cost and keeping nutrient value as a constraint.
Alf
Last edited by Alf; 10-12-2018 at 06:27 AM. Reason: better informatin
This is what I came up with, how would i add the cost?
Layout looks good. I did correct the setting of the object cell from N5 to O2. I also change the instructions a bit referring to the correct cells and cell ranges. See image.
You don't have to specify each cell in the constraint, as you have set a range for min and max value for the solver values you click add and then using the mouse mark the range and add max constraint, then mark the range and add min constraint. This make it less tedious to do and much more readable.
food_solver.jpg
The same way as you have specified "protein", "fat" and "carbs". Just add a column and fill in the values and then use "SUMPRODUCT" formula to calculate total. I don't think there should be set any constraint on costs or? I do think the daily intake of fat calories and carb is the important part.how would i add the cost?
As a friend of mine use to say "If you wish to live cheaply and reach a 100 years of age you should only drink water and only eat Swedish crispbread. And even if you don't get that old if will feel like you did."
How about weight? Will any of these daily food intakes weight more than a normal human can eat? If so perhaps then wight should be added as well with a constraint on max weight?
There may also be cases where solver can't find a solution if constraints are to restrictive. Solver will then pop up a message box telling "Solver could not find a feasible solution" and add an explanation that solver can not find a point for witch all constraints are satisfied.
Alf
Last edited by Alf; 10-15-2018 at 08:57 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks