Calculate a sequence of results until a threshold is reached

[AntiPop]

I'm usually OK at Excel but I'm really struggling with this problem and hopefully somebody can help.

I'm trying to model out a sports league system where, when a team wins, they earn 150 points towards a rating score and if they lose, they have 100 points deducted from that rating score. In this model, I want the team to always win one match then lose one match, in sequence, until their combined score passes a threshold of 1250, at which point I want to count how many matches the team had to play to pass the threshold.

I can simulate this sequence pretty easily if I simply have a sequence of matches where I add or deduct score from the team's previous total (see image), depending on the result, with the results going through the win one, lose one, win one, lose one etc sequence. What I can't figure out how is how to calculate the number of games required (45) to pass the threshold without modelling the full sequence like this. Would anybody be able to help me with this please?

What I have tried so far is getting the delta between a win and a loss (so 50) and dividing the target rating (1250) by that to get the number of matches, but that returns 25 which is clearly wrong.

 

[Sanjeev1976]

"What I have tried so far is getting the delta between a win and a loss (so 50) and dividing the target rating (1250) by that to get the number of matches, but that returns 25 which is clearly wrong"

This is true, if the delta 50 is for 1 match, since 50 is the delta for 2 matches the equation should be 50 * 44 / 2 + 150 (for the last match) = 1,250 i.e. 44 + 1 matches

 

[AntiPop]

"What I have tried so far is getting the delta between a win and a loss (so 50) and dividing the target rating (1250) by that to get the number of matches, but that returns 25 which is clearly wrong"

This is true, if the delta 50 is for 1 match, since 50 is the delta for 2 matches the equation should be 50 * 44 / 2 + 150 (for the last match) = 1,250 i.e. 44 + 1 matches

How do you get the 44 there though? That presumes you know the number of matches before writing the equation, that's the bit I don't know and want to figure out.

 

[Anonymous1378]

You should be able to manipulate the equation to derive 45 from the variables you already know:

50*44/2+150=1250
50*44/2=1250-150
44/2=(1250-150)/50
44=(1250-150)/50*2
45=(1250-150)/50*2+1
Matches=(Threshold-Win)/Delta*2+1

 

[AntiPop]

Thanks for the help, that worked but when I adjust the win and loss values with the same equation it produces wrong results in some scenarios. For example, when the value for a win is 900 and the value for a loss still minus 100, the equation believes it'll take 2 matches to hit 1250, not the correct 3. Ideally I'm trying to figure out an equation that will work for any number of win and lost values, as long as a win is always worth more than a loss.

 

[Anonymous1378]

Thanks for the help, that worked but when I adjust the win and loss values with the same equation it produces wrong results in some scenarios. For example, when the value for a win is 900 and the value for a loss still minus 100, the equation believes it'll take 2 matches to hit 1250, not the correct 3. Ideally I'm trying to figure out an equation that will work for any number of win and lost values, as long as a win is always worth more than a loss.

You can use ROUNDUP() to ensure you get a whole number of matches.

Matches
= ROUNDUP((Threshold-Win)/Delta,0)*2+1
= ROUNDUP((1250-900)/800,0)*2+1