Formulas start to calculate before cells are filled

[csenor]

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.

[TABLE="width: 525"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Push Ups[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age/Count[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]60
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 861"]
<tbody>[TR]
[TD="width: 183, bgcolor: #4472C4"]Name[/TD]
[TD="width: 44, bgcolor: #4472C4"]Age[/TD]
[TD="width: 77, bgcolor: #4472C4"]Sit & Reach[/TD]
[TD="width: 77, bgcolor: #4472C4"]Push Ups[/TD]
[TD="width: 64, bgcolor: #4472C4"]Sit Ups[/TD]
[TD="width: 79, bgcolor: #4472C4"]1.5 Mile[/TD]
[TD="width: 50, bgcolor: #4472C4"]Score1[/TD]
[TD="width: 50, bgcolor: #4472C4"]Score2[/TD]
[TD="width: 50, bgcolor: #4472C4"]Score3[/TD]
[TD="width: 50, bgcolor: #4472C4"]Score4[/TD]
[TD="width: 64, bgcolor: #4472C4"]Average[/TD]
[TD="width: 73, bgcolor: #4472C4"]Result[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[cyrilbrd]

Hi, try =IF(ISBLANK($D6),"",INDEX(Results!$P$11:$V$13,MATCH($B6,Results!$O$11:$O$13,1),MATCH($D6,Results!$P$10:$V$10,-1)))