Altering data depending on conditional format

[boakley]

Hello everyone,

I have a table with 3 different cells that have data in them (F4, H4 and J4). These cells are set as scores. The next cell, L4 has a SUM formula to get the total score of all 3 score cells (G4, I4 and K4).

The scores are for number of push ups, number of sit ups and run time. It is possible to get over 100 in one or all of the events, since there is whats called an “extended scale” but you must have a 100 as a score for all 3 events before any one of then can go over 100.

Example:
If I maxed my push ups and got 100, maxed my situps and got 100, and went OVER max for my run, then that would allow me to get over 100 on my run score since the other events were 100 as well, recieving a total score of 300+.

Now lets say I DID NOT max my push ups and only got a score of 90. But my situps were maxed for 100 points and I went OVER max on my run, I would only recieve the standard 100 points for my run and not over that because my pushups were not worth 100 points.

I would like to have cell L4 display the total score from the three events (event scores based off of different sheets that I created). If one event did not recieve a 100 then None of the other score cells will allow a score for over 100.

Example of table WITH extended scale applied
[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][/TD]
[TD]Pushup Reps[/TD]
[TD]Pushup Score[/TD]
[TD]Situp Reps[/TD]
[TD]Situp Score[/TD]
[TD]Run Time[/TD]
[TD]Run Score[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]77[/TD]
[TD]100
[/TD]
[TD]82[/TD]
[TD]100[/TD]
[TD]1310[/TD]
[TD]102[/TD]
[TD]302[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]78[/TD]
[TD]101[/TD]
[TD]82[/TD]
[TD]100[/TD]
[TD]1316[/TD]
[TD]100[/TD]
[TD]301[/TD]
[/TR]
</tbody>[/TABLE]
*If all scores are 100 or over, extended scale applies and should be totaled as such in Cell L4,L5 (According to the Excercises score sheet)

Example of table WITHOUT extended scale applied
[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][/TD]
[TD]Pushup Reps[/TD]
[TD]Pushup Score[/TD]
[TD]Situp Reps[/TD]
[TD]Situp Score[/TD]
[TD]Run Time[/TD]
[TD]Run Score[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]76[/TD]
[TD]99[/TD]
[TD]83[/TD]
[TD]100[/TD]
[TD]1310[/TD]
[TD]100[/TD]
[TD]299[/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]
</tbody>[/TABLE]
*Even though according to the excercises score sheet doing 83 situps should have gotten him a 101 and the run time should have gotten him a 102, both of those scores revert back to 100 because he didnt get the max score for pushups, REGARDLESS of what the scoresheet says. But the reps stay the same.

Example of Push Up score sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Age[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Reps[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]102[/TD]
[TD]101[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]101[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]76[/TD]
[TD]100[/TD]
[TD]99[/TD]
[TD]99[/TD]
[/TR]
</tbody>[/TABLE]

Example of Situp Score Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Age[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Reps[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]102[/TD]
[TD]101[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]101[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]100[/TD]
[TD]99[/TD]
[TD]99[/TD]
[/TR]
</tbody>[/TABLE]

Example of Run Time Score Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Age[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Run Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1310[/TD]
[TD]104[/TD]
[TD]102[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]1316[/TD]
[TD]102[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1322[/TD]
[TD]100[/TD]
[TD]98[/TD]
[TD]98[/TD]
[/TR]
</tbody>[/TABLE]


I already have the formulas set up in cells G, I and K to get the proper score using INDEX MATCH.

If anyone could help here, it would be fabulous. Thank you in advance.

 

[excelnoob123456]

Try:

=if(and([pushupscore]>100,[situpscore]>100,[runscore]>100),sum([pushupscore],[situpscore],[runscore]),if(and([pushupscore]>100,[situpscore]<100,[runscore]<100),sum(100,[situpscore],[runscore]),if(and([pushupscore]<100,[situpscore]>100,[runscore]<100),sum([pushupscore],100,[runscore],if(and([pushupscore]<100,[situpscore]<100,runscore>100),sum([pushupscore],[situpscore],100),if(and([pushupscore]>100,[situpscore]>100,[runscore]<100),sum(100,100,[runscore],if(and([pushupscore]>100,[situpscore]<100,[runscore]>100),sum([100,[situpscore],100),sum([pushupscore],100,100)))))))))

where [pushupscore], [runscore] and [situpscore] are the cells containing the relevant scores.

Probably an easier way to do it but this should work.

 

[boakley]

@excelnoob123456,
So I entered that formula you sent into the total score cell and all it returned was "FALSE" after I input all of the repetition data into the other cells. I double and triple checked to see if maybe I had entered something wrong but it was the same. Any idea's?

 

[excelnoob123456]

So the idea of the formula is that you need to have an option for every possibility that could happen, whether the score is 100 or not for each exercise, you have three exercises with two options for each (3*2 = 6) therefore 6 possible outcomes, I just checked the formula and I have included all of the outcomes so I don't think the problem is with the formula.

Check whether the data you are looking at is formatted as a number or if the cells which you are refrencing in the formula which contain the scores have a formula in themselves then put =value() around the formula, which will make excel treat it as a number - I have had this issue a lot of times myself.

 

[boakley]

Awesome! Thank you so much! It finally works!