Formula Problem

[boakley]

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.

	F	G	H	I	J	K	L
3	Push Up Reps	Push Up Score	Sit Up Reps	Sit Up Score	Run Time	Run Score	Total Score
4	77	100
5

<tbody>
</tbody>

	F	G	H	I	J	K	L
3	Push Up Reps	Push Up Score	Sit Up Reps	Sit Up Score	Run Time	Run Score	Total Score
4	77	100	82	100
5

<tbody>
</tbody>

	F	G	H	I	J	K	L
3	Push Up Reps	Push Up Score	Sit Up Reps	Sit Up Score	Run Time	Run Score	Total Score
4	77	100	82	100	1316	100	300
5

<tbody>
</tbody>

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.

	F	G	H	I	J	K	L
3	Push Up Reps	Push Up Score	Sit Up Reps	Sit Up Score	Run Time	Run Score	Total Score
4	76	99		100		100	299
5

<tbody>
</tbody>

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.

 

[Dave Patton]

I have not reviewed all the details shown above.
Can you reorganize your sheets to facilitate the calculations that you require?

Consider =IF(AND(G4>100,I4>100,K4>100),G4+I4+K4,MIN(G4,100)+MIN(I4,100)+MIN(K4,100))

 

[boakley]

I thought about changing the sheets as well but after looking at them again, I don't think I can with the way that I need the table set up. I tried your formula and it pretty much did the same thing that was doing before. Like it shows in the first 3 tables that I put on here, with every cell containing number of reps (cells F, H and J) that I input, as long as the reps equal the score of 100, everything is ok and the next score cells will not populate. But if the number of reps gives a score <100, the cell that corresponds with the data entry will populate with the right score but the other score cells will populate with "100" until you put a number of reps or run time in. I just need them to not populate until I enter data in the respective cell , regardless of what the score is.