Results 1 to 10 of 10
Thread: Formula Look Up (2003)

20040117, 17:02 #1
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula Look Up (2003)
I have several IF formulas that very with a persons age. As age changes I would like Excel to change the formula to fit that persons age. See attached file and look under any of the work sheets. I will be adding a birth date column in the future so the age column will change as age increases. Thanks for any and all help.

20040117, 17:08 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Look Up (2003)
"look under any of the work sheets" is rather vague. Can you give a specific example of a formula that you would like to change, and an indication of what you want to accomplish?

20040117, 18:58 #3
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Look Up (2003)
Here is an example. In E2 is the persons age. In E3 is the persons score on a test say 3. In E4 is an if formula ie. =IF(F36<=0,"N/A",IF(F36>=9.9,"Above",IF(F36<8.9, "Below", IF(F36>=9, "Healthy")))). The "Above", "Below", and "Healthy" values change with a persons age. Does this help?

20040117, 21:57 #4
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula Look Up (2003)
I think you may want to start by creating a table of ages and "healthy scores". Then you can compare the actual score of a person with the result from the table. I 've attached a small example. See if it's close to what you're looking for. If so, it can be dressed up a little better to suit your needs...
 Ricky

20040117, 22:17 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Look Up (2003)
For Pushups for example
Create a table like this:
<table border=1><td valign=bottom>Age</td><td valign=bottom>N/A</td><td valign=bottom>Below Ave</td><td valign=bottom>Healthy Zone</td><td valign=bottom>Above Ave</td><td align=right valign=bottom>6</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>3</td><td align=right valign=bottom>9</td><td align=right valign=bottom>7</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>4</td><td align=right valign=bottom>11</td><td align=right valign=bottom>8</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>5</td><td align=right valign=bottom>14</td><td align=right valign=bottom>9</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>6</td><td align=right valign=bottom>16</td><td align=right valign=bottom>10</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>7</td><td align=right valign=bottom>21</td><td align=right valign=bottom>11</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>8</td><td align=right valign=bottom>21</td><td align=right valign=bottom>12</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.001</td><td align=right valign=bottom>10</td><td align=right valign=bottom>21</td></table>
And name it something like "Pushups" [Insert  name define]
Then use this formula to lookup the values.
<pre>=INDEX(PushUps,1,MATCH(F2,INDEX(PushUps,MATCH (E2,INDEX(PushUps,0,1),0),0),1))</pre>
It looks up the age col to get the correct row, then looks in that row to get the col number, then looks up the header.
Create similar tables for each of the "tests"
Steve

20040119, 15:46 #6
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Look Up (2003)
Why should the Below Ave be .001? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

20040119, 16:27 #7
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Look Up (2003)
Also, can this table be located on another work sheet or does it need to be on the same work sheet? My thinking is to have a separate work sheet with all of the tables on it.

20040119, 17:02 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Look Up (2003)
It was a "small" number. What you list in the table is the lower limit for the range. I needed a lower limit for "Below Ave", that was >0, since Zero is "blank".
Steve

20040119, 17:03 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Look Up (2003)
The lookup tables can all be in separate sheets or all on the same "separate" sheet. The just need to be named. The sheet ccan be hidden if desire.
Steve

20040203, 15:15 #10
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Look Up (2003)
Steve,
Thanks for the help!! Everything worked out great and was able to create a mean report with the data!!!
Chris