The Diet Problem

This Excel spreadsheet will help you find the cheapest combination of foods that fulfills a set of desired nutritional requirements.  This is known as The Diet Problem.  It's a classic example of linear programming and originates from the work of George Stigler, an economist who studied this problem in the 1940s. It was only in the 1950s with the development of the simplex algorithm by Dantzig that fast and accurate solutions became available.

Click this picture for an overview of the spreadsheet.


There are three primary steps in using this worksheet.
  1. Specify the upper and lower bounds on your desired nutritional requirements.  You can set minimum and maximum values for the total calories, fat, saturated fat, trans fat, cholesterol, sodium, carbohydrates, fibre, sugar, protein, vitamin A, vitamin C, calcium and iron.
  2. Specify a possible range of foods, their nutritional data and the cost per portion.  I've included a range of foods, but if you want to modify this list then http://www.nutritiondata.com has a database of foods and their nutritional qualities.
  3. Use Excel's Solver to minimize the total cost (number of portions x cost per portion) while maintaining the nutritional requirements by varying the portions of each food (follow the steps in the picture above).

You may need to enable the Solver add-in via File>Options>Add-Ins.  I've saved my Solver model settings in the range A33:A57 in case you want to return to my defaults.

Of course, if you're seriously thinking about using this spreadsheet to optimize your diet, then I seriously suggest you reconsider - life is too short.

In fact, read George Dantzig's 1990 article The Diet Problem, in which he describes his attempt to follow a diet derived from this method.  He nearly overdosed on Bovril Bouillion cubes closely followed by two pounds of bran.  He ended up following his wife's advice and lost over 20 pounds. 


0 comments:

Post a Comment