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".

	F	G	H	I	J	K	L
4	PUSH UP REPS	PUSH UP SCORE	SIT UP REPS	SIT UP SCORE	RUN TIME	RUN SCORE	TOTAL SCORE
5	77	100	82	100	1316	100	300

<tbody>
</tbody>

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?

 

[oldbrewer]

PUSH UP REPS	PUSH UP SCORE	SIT UP REPS	SIT UP SCORE	RUN TIME	RUN SCORE	TOTAL SCORE							push up reps
77	100
													0	0
													10	20
push ups	score												20	40
105	110												40	60
													60	90
													77	100
													85	105
													95	110
													120	115
			just considering push up reps								150	120
			however many push ups achieved
			score is obtained from ref table on right by
			=VLOOKUP(A4,mytable,2)

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[boakley]

PUSH UP REPS	PUSH UP SCORE	SIT UP REPS	SIT UP SCORE	RUN TIME	RUN SCORE	TOTAL SCORE							push up reps
77	100
													0	0
													10	20
push ups	score												20	40
105	110												40	60
													60	90
													77	100
													85	105
													95	110
													120	115
			just considering push up reps								150	120
			however many push ups achieved
			score is obtained from ref table on right by
			=VLOOKUP(A4,mytable,2)

<tbody>
</tbody>

I have other sheets with the scoring platform on them. I used INDEX MATCH to get the scores. It works. Its just that with the formula I provides above: =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)))))))))

There is something with this formula that makes the other scoring cells populate with “100” if one of the three scoring cells is less than “100”.

 

[boakley]

PUSH UP REPS	PUSH UP SCORE	SIT UP REPS	SIT UP SCORE	RUN TIME	RUN SCORE	TOTAL SCORE							push up reps
77	100
													10	20
push ups	score												20	40
105	110												40	60
													60	90
													77	100
													85	105
													95	110
													120	115
			just considering push up reps								150	120
			however many push ups achieved
			score is obtained from ref table on right by
			=VLOOKUP(A4,mytable,2)

<tbody>
</tbody>

Actually, sorry. I put the wrong formula in here after I looked at it again. The formula that is actually messing it up is:

=IF(AND(SUMPRODUCT(--(J5>=100),--(L5>=100))<1,H5>100),100,H5)

 

[oldbrewer]

I have not used -- within sumproduct so I cannot help further

 

[boakley]

Ok. Thank you for your help anyway. Hopefully someone can further assist.

 

[dreid1011]

Where is your formula in #4 located? And if different, what formulas are in G5, I5, K5?

 

[boakley]

Where is your formula in #4 located? And if different, what formulas are in G5, I5, K5?

EXAMPLE #1

	E	F	G	H	I	J	K	L	M
3	GENDER	AGE	PUSH UP REPS	PUSH UP SCORE	SIT UP REPS	SIT UP SCORE	RUN TIME	RUN SCORE	TOTAL SCORE
4	M	31	77	100	82	100	1316	100	300
5

<tbody>
</tbody>

EXAMPLE #2

	E	F	G	H	I	J	K	L	M
3	GENDER	AGE	PUSH UP REPS	PUSH UP SCORE	SIT UP REPS	SIT UP SCORE	RUN TIME	RUN SCORE	TOTAL SCORE
4	M	31	75	98		100		100	300
5

<tbody>
</tbody>

Formulas in each Cell:
G4: I enter the data

G5: =G4

H4: =IF(AND(SUMPRODUCT(--(J5>=100),--(L5>=100))<1,H5>100),"100",H5)

H5: =IFERROR(IF(E4="M",INDEX(MALE_PU!$B$2:$AO$121,MATCH(G4,MALE_PU!$A$2:$A$121,0),MATCH(F4,MALE_PU!$B$1:$AO$1,0)),IF(E4="F",INDEX(FEMALE_PU!$B$2:$AO$121,MATCH(G4,FEMALE_PU!$A$2:$A$121,0),MATCH(F4,FEMALE_PU!B$1:AO$1,0)),"")),"")

I4: I enter the data

I5: =I4

J4: =IF(AND(SUMPRODUCT(--(H5>=100),--(L5>=100))<1,J5>100),"100",J5)

J5: =IFERROR(IF(E4="M",INDEX(MALE_SU!$B$2:$AO$121,MATCH(I4,MALE_SU!$A$2:$A$121,0),MATCH(F4,MALE_SU!$B$1:$AO$1,0)),IF(E4="F",INDEX(FEMALE_SU!$B$2:$AO$121,MATCH(I4,FEMALE_SU!$A$2:$A$121,0),MATCH(F4,FEMALE_SU!$B$1:$AO$1,0)),"")),"")

K4: I enter the data

K5: =K4

L4: =IF(AND(SUMPRODUCT(--(H5>=100),--(J5>=100))<1,L5>100),"100",L5)

L5: =IFERROR(IF(E4="M",INDEX(MALE_RUN!$B$2:$AO$812,MATCH(K4,MALE_RUN!$A$2:$A$812,0),MATCH(F4,MALE_RUN!$B$1:$AO$1,0)),IF(E4="F",INDEX(FEMALE_RUN!$B$2:$AO$584,MATCH(K4,FEMALE_RUN!$A$2:$A$584,0),MATCH(F4,FEMALE_RUN!$B$1:$AO$1,0)),"")),"")

M4: =IF(AND(H4>=100,J4>=100,L4>=100),SUM(H4,J4,L4),IF(AND(H4<100,J4<100,L4<100),SUM(H4,J4,L4),IF(AND(H4<100,J4>=100,L4>=100),SUM(H4,100,100),IF(AND(H4>=100,J4<100,L4>=100),SUM(100,J4,100),IF(AND(H4>=100,J4>=100,L4<100),SUM(100,100,L4),IF(AND(H4>=100,J4<100,L4<100),SUM(100,J4,L4),IF(AND(H4<100,J4>=100,L4<100),SUM(H4,100,L4),IF(AND(H4<100,J4<100,L4>=100),SUM(H4,J4,100)))))))))

M5: =IF(AND(H4>=100,J4>=100,L4>=100),SUM(H4,J4,L4),IF(AND(H4<100,J4<100,L4<100),SUM(H4,J4,L4),IF(AND(H4<100,J4>=100,L4>=100),SUM(H4,100,100),IF(AND(H4>=100,J4<100,L4>=100),SUM(100,J4,100),IF(AND(H4>=100,J4>=100,L4<100),SUM(100,100,L4),IF(AND(H4>=100,J4<100,L4<100),SUM(100,J4,L4),IF(AND(H4<100,J4>=100,L4<100),SUM(H4,100,L4),IF(AND(H4<100,J4<100,L4>=100),SUM(H4,J4,100)))))))))


I have other sheets that have been made (male_pu...male_su...male_run...female_pu...female_su...female_run) that I have used with this work book and utilized INDEX MATCH in order to reference the correct score with the corresponding age, gender and reps amount.

In order for anything to populate, the gender has to be entered in the gender column as well as the age.

In example #1 above, for a male that is 31 years old, 77 pushups would get him 100 points. 82 sit ups would get him 100 points and a 1316 run time would get him 100 points. When I enter the data for any of the exercise events (G4, I4 and K4) as long as they got 100 points, everything else would stay blank until I entered the next exercises data.

When I enter the data and they did not achieve 100 points (example #2 ) 75 push ups got him 98 points and then the other score columns automatically populated with 100's even though there was no reps data entered. They would change to the correct score once I input the reps data, but I want them to stay blank until I put something in for the reps data.

I'm thinking that it has to be an issue with my formula in cells H4, J4 and L4. But I'm lost as of this moment. I hope this clarifies. Do you have any idea's?

 

[dreid1011]

I am unable to replicate your issue. At the moment, I am manually entering scores in where your INDEX formulas are, and it all seems to work as I think I understand from your post.

 

[boakley]

I am unable to replicate your issue. At the moment, I am manually entering scores in where your INDEX formulas are, and it all seems to work as I think I understand from your post.

So your saying that with all the formulas entered the way I have them, and no manually inserted data in cells G4, I4 and K4, everything is blank. And when you enter a reps amount that gives less than 100 pints that populates in H4, nothing populates in J4 and L4 even with no data in I4 and K4? Because it is with mine. I don’t understand this. Lol