1

I’m buying some food, and want the most calories I can get for my money.

I have a budget of $12.00. There are 3 food groups, and I need to pick 2 items from each of those groups. There is also a Dessert group- I have to pick one dessert item. That’s a total of 7 items. You can’t purchase an item more than once.

So here are my items:

ID  Food        Group                           Calories    Price
1   Banana      Fruit (must pick 2 fruits)      160         $.75
2   Apple       Fruit (must pick 2 fruits)      120         $.65
3   Orange      Fruit (must pick 2 fruits)      160         $1.25
4   Sausage     Meat (must pick 2 meats)        260         $3.05
5   Bologna     Meat (must pick 2 meats)        230         $1.15
6   Ground Beef Meat (must pick 2 meats)        310         $3.15
7   Salami      Meat (must pick 2 meats)        320         $2.73
8   Carrots     Veg (must pick 2 vegetables)    70          $1.80
9   Cucumbers   Veg (must pick 2 vegetables)    90          $2.10
10  Bell Pepper Veg (must pick 2 vegetables)    110         $1.35
11  Cake        Dessert (must pick 1 dessert)   350         $2.28
12  Pie         Dessert (must pick 1 dessert)   320         $2.90
13  Ice Cream   Dessert (must pick 1 dessert)   380         $1.86

Can I solve this in Excel using the Solver add-in? Would you mind pointing me in the right direction?

Improve this question

1 Answer 1

Reset to default
1

Unfortunately no. Excel can only vary one item at a time and you have at least 4 variables to change.

But, on the brighter side, you don't need the solver to get the best arrangement. First you calculate the cost per calorie. Select the lowest 2 (or 1) items from each group. You're done.

Improve this answer
4
  • That was my initial reaction, but it isn't necessarily true. The lowest cost per calorie could use items that collectively exceed the $12 limit. This is probably a bad example used to illustrate the problem. In a general case, higher calorie counts within a food group could have higher cost. To stay within budget could require selecting foods with less calories or higher cost per calorie, but less item cost.
    – fixer1234
    Jan 25, 2015 at 3:18
  • @fixer1234 Using the above method, I get 1410 calories for $10.59. Since that leaves me with $1.41, I can include another item or switch items. Since switching items will give me fewer calories for a higher cost, it would be best to add another item. Also, some prices are based on supply and demand. Chuck steak may be $4.99/lb, but sirloin steak is nearly $15.99/lb, yet they have nearly the same calories.
    – LDC3
    Jan 25, 2015 at 3:30
  • In the example as stated, you are right. I assumed this was a made-up example just to illustrate the problem and the values weren't thought out. With real data, there could be conflicting choices. E.g., there could be a sale on bologna in a large economy package with a good price/pound. That could yield the lowest cost/calorie but the item price could be too high. Similarly, the best choices for three of the food groups could leave you with just enough money for a last item with a low item cost but high cost/calorie. For the general case, I think this might need VBA to iterate a solution.
    – fixer1234
    Jan 25, 2015 at 4:17
  • Thanks guys- I think I'm going to have to use MatLAB for this kind of problem. I didn't specify that I was trying to maximize my spend- an additional constraint that would seem to make a difference.
    – Hairgami_Master
    Jan 25, 2015 at 22:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .