• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with tennis league calculation formula


New Member
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



  • 2024 LHTC Women's Doubles Wed & Fri Morning Tennis.xlsm
    23 KB · Views: 6
In cell G2:
If your version of Excel doesn't support XLOOKUP then try:
Last edited: