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?

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 | |
  • 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 '15 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 '15 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 '15 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 '15 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.