I have made a table that allows me to manually input a number of repititions and the next cell gives me the score that is associated with number by using INDEX MATCH.
As long as the score is for each exercise is "100" and over, the next exercise score does not populate and stays white until the manual input of repititions is entered in cells F, H and J. Just like I have shown directly below.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Push Up Reps
[/TD]
[TD="align: center"]Push Up Score
[/TD]
[TD="align: center"]Sit Up Reps
[/TD]
[TD="align: center"]Sit Up Score
[/TD]
[TD="align: center"]Run Time
[/TD]
[TD="align: center"]Run Score
[/TD]
[TD="align: center"]Total Score
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]77
[/TD]
[TD="align: center"]100
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Push Up Reps
[/TD]
[TD]Push Up Score
[/TD]
[TD]Sit Up Reps
[/TD]
[TD]Sit Up Score
[/TD]
[TD]Run Time
[/TD]
[TD]Run Score
[/TD]
[TD]Total Score
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]77
[/TD]
[TD]100
[/TD]
[TD]82
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Push Up Reps
[/TD]
[TD]Push Up Score
[/TD]
[TD]Sit Up Reps
[/TD]
[TD]Sit Up Score
[/TD]
[TD]Run Time
[/TD]
[TD]Run Score
[/TD]
[TD]Total Score
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]77
[/TD]
[TD]100
[/TD]
[TD]82
[/TD]
[TD]100
[/TD]
[TD]1316
[/TD]
[TD]100
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If the score is below"100", the rest of the scores populate as "100" until I manually input the number of reps for that exercise. As shown directly below.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Push Up Reps
[/TD]
[TD]Push Up Score
[/TD]
[TD]Sit Up Reps
[/TD]
[TD]Sit Up Score
[/TD]
[TD]Run Time
[/TD]
[TD]Run Score
[/TD]
[TD]Total Score
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]76
[/TD]
[TD]99
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]299
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formula that I have for cell L4 is :
=IF(AND(G4>=100,I4>=100,K4>=100),SUM(G4,I4,K4),IF(AND(G4<100,I4<100,K4<100),SUM(G4,I4,K4),IF(AND(G4<100,I4>=100,K4>=100),SUM(G4,100,100),IF(AND(G4>=100,I4<100,K4>=100),SUM(100,I4,100),IF(AND(G4>=100,I4>=100,K4<100),SUM(100,100,K4),IF(AND(G4>=100,I4<100,K4<100),SUM(100,I4,K4),IF(AND(G4<100,I4>=100,K4<100),SUM(G4,100,K4),IF(AND(G4<100,I4<100,K4>=100),SUM(G4,I4,100)))))))))
The reason for this code is so that if one or more of the exercises does not score at least "100", then the other exercises that DID score OVER "100", will automatically revert back to "100" because you cannot be on the extended scale unless you receive a max score in all three events.
Can anyone help me with the formula? I would like to have the scores populate with each individual exercise if possible.
As long as the score is for each exercise is "100" and over, the next exercise score does not populate and stays white until the manual input of repititions is entered in cells F, H and J. Just like I have shown directly below.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Push Up Reps
[/TD]
[TD="align: center"]Push Up Score
[/TD]
[TD="align: center"]Sit Up Reps
[/TD]
[TD="align: center"]Sit Up Score
[/TD]
[TD="align: center"]Run Time
[/TD]
[TD="align: center"]Run Score
[/TD]
[TD="align: center"]Total Score
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]77
[/TD]
[TD="align: center"]100
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Push Up Reps
[/TD]
[TD]Push Up Score
[/TD]
[TD]Sit Up Reps
[/TD]
[TD]Sit Up Score
[/TD]
[TD]Run Time
[/TD]
[TD]Run Score
[/TD]
[TD]Total Score
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]77
[/TD]
[TD]100
[/TD]
[TD]82
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Push Up Reps
[/TD]
[TD]Push Up Score
[/TD]
[TD]Sit Up Reps
[/TD]
[TD]Sit Up Score
[/TD]
[TD]Run Time
[/TD]
[TD]Run Score
[/TD]
[TD]Total Score
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]77
[/TD]
[TD]100
[/TD]
[TD]82
[/TD]
[TD]100
[/TD]
[TD]1316
[/TD]
[TD]100
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If the score is below"100", the rest of the scores populate as "100" until I manually input the number of reps for that exercise. As shown directly below.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Push Up Reps
[/TD]
[TD]Push Up Score
[/TD]
[TD]Sit Up Reps
[/TD]
[TD]Sit Up Score
[/TD]
[TD]Run Time
[/TD]
[TD]Run Score
[/TD]
[TD]Total Score
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]76
[/TD]
[TD]99
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]299
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formula that I have for cell L4 is :
=IF(AND(G4>=100,I4>=100,K4>=100),SUM(G4,I4,K4),IF(AND(G4<100,I4<100,K4<100),SUM(G4,I4,K4),IF(AND(G4<100,I4>=100,K4>=100),SUM(G4,100,100),IF(AND(G4>=100,I4<100,K4>=100),SUM(100,I4,100),IF(AND(G4>=100,I4>=100,K4<100),SUM(100,100,K4),IF(AND(G4>=100,I4<100,K4<100),SUM(100,I4,K4),IF(AND(G4<100,I4>=100,K4<100),SUM(G4,100,K4),IF(AND(G4<100,I4<100,K4>=100),SUM(G4,I4,100)))))))))
The reason for this code is so that if one or more of the exercises does not score at least "100", then the other exercises that DID score OVER "100", will automatically revert back to "100" because you cannot be on the extended scale unless you receive a max score in all three events.
Can anyone help me with the formula? I would like to have the scores populate with each individual exercise if possible.