Next Up Previous Contents Index
Next: Eat watch in Up: Choose the chow Previous: Doris plans a

Meal planning with Excel


Rather than do lots of arithmetic and flipping through calorie tables, you can use the MEALPLAN.XLS worksheet and Excel to expedite the selection of foods. When you load the worksheet, you'll see a screen like this:

Note that a new MEAL menu has appeared at the right of the menu bar, after the standard Excel menus. To clear any previous meal, select MEAL CLEAR MEAL. The ``Meal total'' in the upper right of the screen will be blank, indicating no foods are selected for the current meal. You include foods in a meal simply by scrolling the display to the desired food (they are organised by food groups such as ``Fish,'' ``Dairy,'' and ``Vegetable'' in alphabetical order, then alphabetically within a group by the name of the food), then entering the quantity of food you intend to eat, in terms of the given ``Serving'' size, in the ``Quantity'' cell. The ``Calories'' column gives the number of calories per serving of each food so you can easily see the consequences of including a given food in your meal.

Here we're in the Vegetable aisle, entering the ingredients for the green salad Doris had for dinner. We've entered 2 in the Quantity cell for iceberg lettuce, since two cups will be included. The Total column shows the lettuce contributes a mighty 14 calories toward the daily goal. Next, we enter 1 in the Quantity field for onions, throwing a cup of chopped onions into the salad bowl (that may be a bit much, but perhaps Doris is taking a plane trip tomorrow and wants to deter her seatmate from striking up idle conversation). These are the first two items included so far in the meal and their total calories, 79, appears as the Meal total at the upper right.

Scrolling back and forth through a huge list of foods isn't much of an improvement over a printed table. Excel's DATA FORM facility, however, lets you retrieve foods from the database based on a wide variety of selection criteria.

Doris is shopping for steak. After pressing the ``Criteria'' button to specify constraints on the foods selected, she's entered ``meat'' as the food group (to avoid seeing frozen entrees such as ``Stuffer's Iguana Steak with Amanita Gravy,'' or ``Cruft Pasteurised Processed Steak-like Substance'' in the dairy case), and ``*steak'' as the Food category to choose all items containing the word ``steak.'' Finally, in the interest of hitting the calorie target, Doris has specified ``<250'' in the Calorie field so only items with that number of calories or fewer need apply. Pressing the ``Form'' button returns Doris to the data form, allowing her to find foods that meet these criteria. Each time she presses ``Find Next,'' the next qualifying food appears. After a few presses, Doris is tempted by a Sirloin steak.

When she settles on a porterhouse steak, she simply enters 1 in the Quantity field of the data form to add that item to the menu, with the standard serving size. If she planned on eating 6 ounces of steak, she'd enter 1.5 for the Quantity to indicate one and a half times the standard serving.

As each item is selected, the Meal total increases. If the meal exceeds the allowed calories, items can be removed by scrolling through the database or by using the DATA FORM to select items with a Quantity >0 and present them for review.

When a meal has been composed to your satisfaction, choose MEAL EXTRACT MEAL to create a new worksheet containing a summary of the food included in the meal. When the meal summary worksheet appears, the cell containing its title is highlighted; enter an appropriate description (for example, ``Sunday Dinner'') if you wish.

You can print a copy of the menu to use as you prepare the meal, or save it on disc for future reference, as you like.

When you're trying to adjust a meal to a given number of calories, it's useful to see which components of the meal account for most of the calories. Choosing MEAL CHART CALORIES generates a pie chart showing the relative calorie contribution of each item comprising the current meal.

The pie chart often points out aspects of a menu that don't jump out as obviously from a table of numbers. Doris might be startled to discover just how many of the calories in her dinner came from that single one tablespoon dollop of blue cheese salad dressing. The pie chart fingers it as the third largest calorie item, accounting for more than 12% of the entire calories in the meal. Given this information Doris might, for example, switch to a low-fat dressing that contains only 3 calories per tablespoon. It might not taste as lush and creamy, but the calories saved would allow her to add, for example, an ear of corn on the cob and still wind up with fewer calories.

If you frequently eat foods not included in the MEALPLAN database, you can easily add them. Use EDIT INSERT to open a blank row for the new item, enter its description in the same format as the other entries, then select the Total cell of the line above and the Total cell of the new line and use EDIT FILL DOWN to copy the formula that computes the total calories onto the new line. Test the new item by entering 1 in its Quantity cell and make sure the Total for the item and the Meal total at the upper right reflect the calories in the new food. You can use the DATA SORT command to re-sort the food database any way you like: by food name, by number of calories, or by a combination of criteria. Refer to the Excel manual for details.

Next Up Previous Contents Index
Next: Eat watch in Up: Choose the chow Previous: Doris plans a

By John Walker