Need Help with question using Excel Solver

matt23conley

New Member
Joined
Sep 10, 2013
Messages
2
I need some help with the following question using excel solver:

A diet is being prepared for college students. The objective is to feed the students at the least amount of cost, but the diet must be between 1800 and 3600 calories. No more than 1400 calories can be starch, and at least 400 calories have to be protein. The varied diet is made of 2 foods: A and B. Food A costs $.75/lb and contains a total of 600 calories, 400 protein and 200 are starch, but no more than 2 pounds of Food A can be used. Food B costs $.15/lb and contains 900 calories, of which 700 are starch, 100 are protein, and 100 are fat.

a.)Write the equations representing this info
b.)Solve the problem using Microsoft Excel Solver

I can't figure out how to make the variables.
I first started with:
X1=calories of starch
X2=calories of protein
X3=calories of fat

then constraints of:
1800<=x1+x2+x3<=3600
x1<=1400
x2>=400

then I can't figure how to apply the other constraints of using only 2 pounds of Food A.

I also can't figure out what the objective function would be.

Please help. Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
Matt - you're coming at this from the wrong direction. The calories of starch, protein and fat should be outputs, not inputs.

The inputs should be the pounds of Food A used (I named this Alpha) and the pounds of Food B used (Beta).

Then, you can create formulas to represent the total calories of Starch, protein, fat and the overall cost. For example, Cost = 0.75*Alpha + 0.15 *Beta.

The objective is then to minimise the overall cost. The 2 pounds of Food A restraint is satisfied by setting Alpha <= 2.
 

matt23conley

New Member
Joined
Sep 10, 2013
Messages
2
Does this look better for constraints:

1800<=Alpha+Beta<=3600
Alpha<=2
200*alpha+700*beta<=1400
400*alpha+100*beta>=400
 

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
The first one isn't right yet.

The number of calories is 600*Alpha + 900*Beta
 

Forum statistics

Threads
1,137,120
Messages
5,679,729
Members
419,853
Latest member
hc9587

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top