Formula Problem

boakley

New Member
Joined
Feb 8, 2018
Messages
36
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top