I'm not the greatest as far as maths is concerned so please bare with me if I have made some fundamental errors here in my approach to dealing with this problem.
I have recently been trying to make a spreadsheet that updates any team/individuals odds to win an event as that event progresses.
The main stipulation of my spreadsheet is that it needs to be in a sport where you are required to win "x" amount of sets/legs to win the game. For example first to 3 sets in tennis or volleyball or first to 15 frames in a snooker match. This wouldn't work on sports like soccer, baseball etc becuase in sports like that the performance isn't capped (you can win 8-0. 9-0 or 10-0 in soccer but you can only ever win 3-0 in a tennis game at best)
What I have so far is:
Price of Team A (in % terms) = pa
Price of Team B (in % terms) = pb
First to (what they need to win) = f
Current score of Team A = sa
Current score of Team B = sb
New price of Team A (in % terms) = na
New price of Team B (in % terms) = nb
I have then come up with this formula: na=pa+(((100-pa)/f)*sa)
This works perfectly to create a new price for a team winning any number of sets to nil, but I cannot figure out how to get it to understand that a team is winning 4-3 and not 4-0. I am hesitant to use the score difference because I don't think that in a best of 5 sets game a score of 4-3 is equal to 1-0 (because team A only needs 1 set to win and team b needs 2, which must be different to team A needing 4 sets and team b needing 5).
I have applied the same formula to Team B as well and as you would expect because it doesn't understand that Team B are now 1 set down to Team A, their % chance of winning the game remains the same (or in fact rises if I enter a score of 4-2 for example) when in fact it should be dropping to reflect what is happening in the game.
I have considered entering another field of "sets required to win" or "score difference" but cannot figure out how to apply them to the formula that I already have.
Any help would really be appreciated, I think that as far as it goes, what I have is pretty much 70% of the way there, I just need that little bit of tinkering to make it work properly so if anyone has any suggestions I'd be interested to hear them.