# Thread: Formula Look Up (2003)

1. ## 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.

2. ## 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?

3. ## 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?

4. ## 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...

5. ## 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

6. ## 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>

7. ## 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.

8. ## 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

9. ## 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

10. ## 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•