Results 1 to 10 of 10
  1. #1
    Lounger
    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.

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

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

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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

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

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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

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

Posting Permissions

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