Need help with tennis league calculation formula

[mknights]

Hi all, I'm coming up with a tracking system for social tennis league programs whereby each person starts with 100 points. Each session 12 points (4 points max per rotation x 3 rotations) can be won and if you score between
0 and 1 you lose 10 points,
between 2-3 you lose 6,
between 4-6 you don't lose any,
between 7-8 you gain 3,
between 9-10 you gain 6 and
between 11-12 you gain 10.

So, there's a starting points column, 3 rotation columns and then a total points for the night column and then one last column that is going to take the average of your last 3 results once you attend 3 times and that score is what will ultimately determine which court you get assigned to when you next attend, as it compares to the other people that show up. This will make it even so that if you disappear for awhile due to holidays etc it won't alter which court you get assigned too that much.

Anyway, I have it working except for the fact that if I leave the 3 rotation columns blank the total score for the night reduces by 10 points and I can't figure out why. If there's no entry in each of the columns I want the total score to stay what it was at the start of the night. If someone could help that'd be amazing. I've attached the spreadsheet.

Thanks in advance

mike

 

[p45cal]

In cell G2:

=C2+IF(COUNTBLANK(D2:F2)=3,0,XLOOKUP(SUM(D2:F2),{1;3;6;8;10;12},{-10;-6;0;3;6;10},"!",1))

If your version of Excel doesn't support XLOOKUP then try:

=C2+IF(COUNTBLANK(D2:F2)=3,0,INDEX({10;6;3;0;-6;-10},MATCH(SUM(D2:F2),{12;10;8;6;3;1},-1)))

 

[mknights]

I just wanted to say thanks so very much for figuring that out for me. Works flawlessly.

Mike