TrenchcoatChicken wrote:
(I)n Numbers '09 there is a weight loss template,
in the MET data there is a table for Walking and Running. I ideally want to know how to add additional tables for use.
I've created my second table and populated it with all the correct information but I'm at a loose end as how to link this in with the main document so I can select it from the activity menu.
Hi TC,
Welcome to Apple Discussions and the Numbers '09 forum.
(puts on swami cap and pulls out crystal ball)
If I understand correctly, you want to add more choices to the Activity column of Step 3, and have them included in the calculations in the MET column of the same table.
The existing formula is this one (rearranged to break the lines at logical points):
=IF(E2="Weight Training",6,
IF(AND(E="Running",G=""),10,
IF(AND(E="Walking",G=""),2.5,
(INDEX(Walking and Running MET :: $A:$B,MATCH(F2/G2,Walking and Running MET :: $A,-1)-1,2)))))
The first three lines assign specific MET values if the activity is Weight Training (6), Running with no distance specified (10) or Walking with no distance specified (2.5).
If none of these conditions are met (with the provided template, that would occur when the Activity was walking or Running, and a distance had been entered in column G (Distance Travelled) ), then the last line comes into play.
Working from the inside brackets outward (and moving to row 5, where this part of the formula is first called in the sample template):
MATCH(F5/G5,Walking and Running MET :: $A,-1)
F6/G6 is 'minutes per mile', the value that MATCH is to search for in Column A of the MET data table. The third argument, "-1" means MATCH is to find the 'smallest value in the range that is greater than or equal to' F5/G5. MATCH returns the position of that value in the list.
For the values on row 6, F5/G5 is 12 minutes/1.35(miles), or 8 minutes 53 seconds, which matches 8 minutes 30 seconds and returns its position (9) on the list.
That value is returned to the formula, where 1 is subtracted from it to make INDEX retrieve a value from the row above the matched row in the table:
(INDEX(Walking and Running MET :: $A:$B,8,2)
INDEX returns the value that is in the 8th row and second column (8,2) of the range specified ($A$B = columns A and B ) of the MET table (11.0). Note that row 1, a header row, is not included in the range specified.
To read a MET table for another activity, you will need to place the existing INDEX part of that formula into another IF statement, then add a new INDEX part to pick the MET off the new MET table. You will also need to add the activity to the popup menus (see note below), and possibly add one or more columns to the Step 3 table to accept the data used to calculate the value to be MATCH the first column of the new table. Without knowing the type of data you are using, it's not possible to do more than sketch out the formula.
=IF(E2="Weight Training",6,
IF(AND(E="Running",G=""),10,
IF(AND(E="Walking",G=""),2.5,
IF(OR(E="Walking",E="Running"),(INDEX(Walking and Running MET :: $A:$B,MATCH(F2/G2,Walking and Running MET :: $A,-1)-1,2),
(INDEX(New MET :: $A:$B,MATCH(X2/Y2,New MET :: $A,-1)-1,2))))
The fourth IF statement will activate the existing INDEX statement for Running or Walking activity where a distance has been specified. The (new) last section will activate for any activity that is not Walking, Running, or Weight Training.
Replace X2/Y2 with the calculation necessary to determine the values in column A of the New MET.
Regards,
Barry
Note: When adding to popup lists note that popups Filled Down fill the selected value as well as the new list. You m ay want to fill only from the first unused line, or make a written list of the values in
existing cells before Filling so you can easily reset them after the change.
B
