Table Tennis Predictor

[davidofyfea]

Hi,

I have a question regarding predicting the outcome of a table tennis match once it has started.

I want to be able to predict the probability of either Player A or Player B winning the match based on the current score. Here are some general points:

To win a match a player has to win 3 Sets
To win a set they have to win at least 11 points
If they are tied at 10-10 in points then to win a set they play until a player wins by two clear points.

I have started to create this myself in Excel as i have the probabilities of each player winning the "next point" yet it is extremely time consuming and i am wondering if there is a way to do this using VBA. It is easy easy to calculate a player winning a set say, 11-0 at the start as it just the probability^11, similarly if they are winning 1-0, it is just ^10 etc. etc. yet when we look at the chances of a player winning a set 11-6 for example it throws up a huge number of different combinations of doing this, i.e they win 3, lose 3, win 2, lose 2, win 3, lose 1, win 3 etc. etc. I also need the probabilities to constantly adjust as the scores update so if it is 3-3, what are the chances now of the score being 11-6, 11-9, 8-11, 10-12 etc etc.

Effectively it would need the probabilities of each outcome for each set and have these displayed and continually updated as the score is updated, i.e:

Player A 11-0 - 1%
Player A 11-1 - 4%
Player B 11-4 - 5%

and so on.

Any help is appreciated and if more info required then please let me know.

Thanks

[FDibbins]

If you have already started something, can you share that with us, and show what you expect?

[davidofyfea]

Hi,

Excel file attached.

I have started it in a more basic way due to the fact as mentioned before it becomes far too detailed when the rules are that a player has to reach 11, the higher the number the more the different combinations there are.

As such, i have shown what is required using the rule that it is the first to 3 points and shown the different combinations this can happen for 3-0 and 3-1.

I have created some lookups which go to the model tab and when we enter an updated score this should be populated automatically. My VBA knowledge is non existent so i have not looked into this.

Thanks.

[davidofyfea]

https://math.stackexchange.com/quest...n-tennis-score

This gives an idea of what i need. However ideally i need each of the permutations displayed so i can then apply the probabilities to them at any given point in the set/match.

https://www.youtube.com/watch?v=9jtcKLjMgcc

This also has potential, we would need say 18 points with either player A or Player B and then create all the combinations. If it was to go to 9 points each then it is fairly easy to calculate who wins.

[MrShorty]

Any details on how you want to generate the permutations? In the limited time I have thought about it, I am having trouble seeing a concise algorithm for generating all permutations. I also suspect that there are a lot of permutations. When I consider that somewhere within the "possible" permutations are sets that end at 10 to 10 then trade points conceivably indefinitely, I wonder if there are limitations to place on "all" possible permutations.

If you have an algorithm for generating all of the permutations, help us understand it and we should be able to help you program that algorithm into Excel.

If you are having trouble coming up with an algorithm for generating the permutations, off the top of my head, I think of something based on "minimal changes" where I start with player A winning all 33 points, player B winning 1 of 34 points (and all variations of that scenario), player B winning 2 of 35 points, and so on until I reach the permutation where player B wins all 33 points.

It seems that every time I really start to think about this, I end up in a Monte Carlo type mode -- where you "play" the match from the specified starting point 100000 times, then analyze the results to estimate the probability of each outcome. It seems like there will be so many possible permutations that it might be preferable to estimate probabilities on a random sample of permutations rather than try to generate every possible permutation.

I'm not sure I understand all the details of how you want to do this, but perhaps those ideas will spark some discussion and allow you to explain in more detail exactly what you want to do and don't want to do.

[davidofyfea]

Hi,

Thanks for the reply.

I have attached another sheet showing how the permutations can be calculated after i have thought about it more,
the sheet shows all the permutations for the first 10 points, generating these is not as hard as i thought as we should be able to take the previous
sequences and simply add on a A or a B for the next point, so it is just a series of duplications (I think). You will see how i have done this by looking at the formulas.

With your point regarding the infinite sequences when it reaches 10-10, my logic here would be that if we get to that point where it is 10-10 then we effectively treat
this like a new calculation and effectively all we want is a player to win 2 in a row, so we effectively have it level, either player one point ahead, back to level
or the game is won etc etc. I believe there is a quick mathematical calculation for this which i need to research.

What i am thinking the key is for me is to look at the current status, so for example we have had 3 points and it has gone AAA, i would like to know from that point
what are all the possible sequences left that have had this exact start AAA, from this i think we can then work out the chances of what the particular score could be.

It is also key that if we have had say 14 points, we need to determine whether the set is still active or not, so for example if it has just gone 11-3 the set is over.

Thanks.

[MrShorty]

It looks like you have a working algorithm for generating the permutations. Here's how I would probably proceed:

1) Continue with the same algorithm and generate all of the permutations up to 22 points. If my calculations are correct, this will be a total of about 8.5E6 total permutations for 2 to 22 points.
2) Test the permutations and identify those that represent "end of set", "still playing", and "invalid". Remove the "invalid" permutations from the set.

As a note at this step, I don't do this programming much, so I find that I often struggle between whether it is better to generate a permutation then reject it or to figure out how to identify which are invalid before generating the permutation. It usually seems easier to me to generate and then reject permutations rather than try to avoid generating invalid permutations. Perhaps as you continue expanding your algorithm, you will see a way to avoid generating the invalid permutations rather than rejecting them after the fact.

3) A filter step to extract all of the "end of set" permutations (as values, not formulas) and take those values into the sheet where I am going to analyze probabilities. At this point, I should never need to repeat steps 1 to 3, so I close/extract the permutation generator and never look at it again (unless something changes so I need to revisit the permutation identification step.
4) Then analyze the probabilities, which you have indicated that you already know how to do.
5) Then, as you indicate, you can analyze the "tied at the end of 22 points" scenario separately.

Other than the number of permutations to generate, do you have any questions about generating the permutations?

[davidofyfea]

Hi,

I looked to have done this all. The file can now calculate the different probabilities one each new point is entered for either player A or B.

The only issue is the file size is 55MB so the whole process is too slow. I will probs need to look into using Python to do this quicker.

Thanks.