Alter the formula for a cell

[boakley]

I have 6 cells that have certain data either in them or populates in them.

The formula that I currently have for the total score (unhidden row 5) is =SUM(G228,I228,K228)

The formula that I have for the total score (hidden row 6) is =IF(AND(G228>=100,I228>=100,K228>=100),SUM(G228,I228,K228),IF(AND(G228<100,I228<100,K228<100),SUM(G228,I228,K228),IF(AND(G228<100,I228>=100,K228>=100),SUM(G228,100,100),IF(AND(G228>=100,I228<100,K228>=100),SUM(100,I228,100),IF(AND(G228>=100,I228>=100,K228<100),SUM(100,100,K228),IF(AND(G228>=100,I228<100,K228<100),SUM(100,I228,K228),IF(AND(G228<100,I228>=100,K228<100),SUM(G228,100,K228),IF(AND(G228<100,I228<100,K228>=100),SUM(G228,I228,100)))))))))

That allowed the event score cells (cells G, I and K) to revert back to "100" if the score in one of the events did not reach the max score of "100", since there is an extended scale that would allow for a score above "100".
[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]4[/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]5[/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My problem is that, when I manually input the number of reps achieved, in cells F5, H5 AND J5, if the number of reps doesn't give a score of "100", all three score cells populate with "100 until something is manually entered into the events reps cells. For example,

If I enter 77 push ups into F5, 100 would populate into cell G5 because 77 push ups equals 100 points. Cells I5 and K5 would remain empty. But if I enter 76 or lower reps into F5, that would give a score of 99 and would make cell G5 populate with the "99" or lower but cells I5 and K5 also populate with "100" for some reason.

I want the score cells (G5, I5 and K5) to remain empty until I manually input some thing their respective reps cells.

I know it has something to do with the formula. Can anyone help?

 

[dreid1011]

Pretty much. The only thing I couldn't/didn't test was the Index formulas to automatically pull in a score based on reps/sex/age. So, there could be something going on with that process that I was unable to see. But, based on my testing, I was manually entering scores less than 100 and the other cells were remaining blank even with no reps entered.