Excel 2013
I created a Physical Fitness Test workbook that scores participants on four events. Scores are based on the person's age and counts on the exercise. For example, a 22 year old has to do 55 pushups while a 42 year old only has to do 44 to get 100%. I created four tables with a two way lookup. Ages are in the rows and counts are in the columns.
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
My formulas for calculating the score is:
=INDEX(Results!$P$11:$V$13,MATCH($B6,Results!$O$11:$O$13,1),MATCH($D6,Results!$P$10:$V$10,-1))
Each score points to the appropriate table.
This is my header to my results table:
<tbody>
</tbody>
My issue is when I add a new Excel Table Row to the sheet, I add the name and age. As soon as I add the age, Score1 and Score4 become N/A. This is ok, however, Score2 and Score 3 become 40 (the lowest score in their prospective tables). When I evaluate the formulas, it tells me that there is a 0 in the Pushups or Situps row. I haven't even entered anything in those cells yet.
Why are 2 of the formulas doing one thing while the other 2 are returning something else?
Thank you.
I created a Physical Fitness Test workbook that scores participants on four events. Scores are based on the person's age and counts on the exercise. For example, a 22 year old has to do 55 pushups while a 42 year old only has to do 44 to get 100%. I created four tables with a two way lookup. Ages are in the rows and counts are in the columns.
| Push Ups | |||||||
| Age/Count | 80 | 45 | 40 | 35 | 30 | 25 | 20 |
| 20 | 100 | 90 | 80 | 70 | 60 | 50 | 40 |
| 31 | 100 | 100 | 90 | 80 | 70 | 60 | 50 |
| 41 | 100 | 100 | 100 | 90 | 80 | 70 | 60 |
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
My formulas for calculating the score is:
=INDEX(Results!$P$11:$V$13,MATCH($B6,Results!$O$11:$O$13,1),MATCH($D6,Results!$P$10:$V$10,-1))
Each score points to the appropriate table.
This is my header to my results table:
| Name | Age | Sit & Reach | Push Ups | Sit Ups | 1.5 Mile | Score1 | Score2 | Score3 | Score4 | Average | Result |
<tbody>
</tbody>
My issue is when I add a new Excel Table Row to the sheet, I add the name and age. As soon as I add the age, Score1 and Score4 become N/A. This is ok, however, Score2 and Score 3 become 40 (the lowest score in their prospective tables). When I evaluate the formulas, it tells me that there is a 0 in the Pushups or Situps row. I haven't even entered anything in those cells yet.
Why are 2 of the formulas doing one thing while the other 2 are returning something else?
Thank you.
