Numbers - Nutrition Input Help

Hi Tai-kun,

You've thought it out well, and have arrived at the most likely solution—a Lookup table to store the basic data, and a calculation to produce the actual results.

Here's what it might look like in Numbers.

The upper table (Table 1) is the lookup table. The data in columns B through E were back-calculated from the figures in your recipe, then pasted into the lookup table as fixed values. Each is the amount for 100g of the named ingredient.

The lower table (Table 2) is the recipe table shown in your post. The Ingredients list and amount columns contain entered data, as does Row 1. All other values in the xample are calculated using the VLOOKUP formula shown below the tables The formula is entered in cell C2 of Table 2, then filed right to column F and down to row 9. (I went to row 10 to show the error triangles that are generated when VLOOKUP is told to look for the contents of an empty cell.

I haven't included the bottom two rows as you've already got the formulas for those.

If you want to include extra ingredient rows in the table, you can avoid the error triangles by wrapping the formula in an IF statement that acts as a switch, preventing VLOOKUP from searching when there's an empty cell in column A, and nothing to search for:

Existing:

C2: VLOOKUP($A2,Table 1::$A:$E,COLUMN()−1)×$B2÷100

Revised: IF(LEN(A2)<1,"",VLOOKUP($A2,Table 1::$A:$E,COLUMN()−1)×$B2÷100))

The part in bold is the original formula. The added part is in normal type weight.

Regard,

Barry

Typo on my part. The formula is filled right and down to place it in every cell of the range C2 to F9.

There are two ways to do this:

After entering the formula into C2:

• Select C2 (single click)
• Bring the mouse pointer close to the right end of C2.
• When a yellow dot (the Fill Handle) appears on the right cell boundary, place the tip of the pointer on it, press and hold the mouse button and drag right to fill the formula into D2, E2 and F2.
• Release the mouse button.
• Bring the pointer close to the centre of the bottom boundary of cells C2 - F2.
• When the Fill handle appears on that boundary, 'grab' it and drag down to fill the formulas into rows 3 to 9.
• Release the mouse button.

Alternate method:

After entering the formula into C2:

• Select (single click) cell C2.
• Press command-C to Copy the cell.
• Shift-click (press and hold the Shift key and click) cell F9

(This expands the selection to include all cells in the range C2 to F9)

• Press command-V to paste the formula into all cells in the range.
• Done

Regards,

Barry

From Numbers Help in your Help menu:

Filling is done by drawing the yellow dot. That way you only have to type in a formula once.

SG

Column A of each of these sheets is populated by a pop up menu from which you can make selections.

When you add an item to the Food Items list, it will not automatically become part of the pop up for the other sheets. What I do after adding items is select all of column A, change the cell format to text, sort alphabetically, then change the format to pop up menu. Next, select one of the "-----" cells from column A, copy it, and paste it to all cells in column A of an unfilled "recipe" or "day' sheet (like the sheet I have that is called "BLANK"). Use that sheet as the template for future recipes/days (i.e., duplicate it when you want to make a new recipe or a new day).

You're welcome, Tai-kun. I in turn appreciate your attention to the process and that you ask when there are things that aren't clear.

Regards,

Barry

Here is one I started

https://www.dropbox.com/s/iw920x84hj4mcmp/Nutrition%20Spreadsheet.numbers?dl=0

1) Food items: manually entered off of the labels or internet. For some I used some math to create the portion sizes I typically use (like a full can of tomatoes vs a 1/4 can serving).

2) There are a few sheets with the ingredients for recipes. I'll create them then copy the resulting nutrition data and create a food item (all the "homemade" items on my list)

3) The same sheet can be used to add up your nutrition for the day.

I would like to post more information about the popup menus in column A but I am having a lot of difficulty with the forum this morning. I will try to continue in a new post