Excel / Maths problem - please help!

Stuck_1963

Thanks Debitcardmayhem.  I really appreciate it (btw, I get 1642, not 1600 dead).

Yes, the 60% target (from my more recent and different style of playing of 59.36%) would be a better reflection of where I am and what to aim for, but I play online (for free) and so use their tally of games played to date which has been recorded since years ago.

If I were to open a new, fresh account, yes, I'd get a better reflection of my true win rate, around the 60% mark, but I'd lose all my levels.

From your kind work, I can embellish and extract even more data now.

So, thanks once again.

Heedtheadvice

Another way of achieving the same sort of answer in general is to use goal seek.

It is (if I remember correctly) part of the Analysis add-in.

With it you can have a formula or a series of calculations and you can then define what you want the answer to be and which cell to vary to get to that answer.

As a simple example consider a x b = c .

a  is four and you want c to be 20.

So goal seek you as it to make c 20 by varying b.

Easy enough example but Excel will do it for you with even (more) complicated calculations.

debitcardmayhem

Chino said:

debitcardmayhem said:

n = 85.76 / .0536 = 1600 (which is 40 squared hence the coded msg to stragglebod 40^2)

I calculate that the OP actually needs to play 1,606 more games ( 9894(0.5313 - 0.54) / (0.54 - 0.5936) ).

Yeah but with your calculation .5313 * 9894 equates to 5256.6822 wins I allowed for the fact that its not possible to win .6822 of a game, so I rounded the wins to 5257

Stuck_1963

Thanks everyone - really, really appreciate it.

I thought I'd share the sad text I now have in my spreadsheet (using the CONCATENATE function) thanks to your assistance, which gives me a daily snapshot of where I am in playing Backgammon online.....

"At the MADAP rate of 59.36% I'd have to play 116 more games and win 69 of those to get to 53.2%, bringing the total games to have been played to get there to 10011 (Wins = 5326, Losses = 4685)."

I really need to get out more.....

inspectorperez

This thread really needs a health warning 😊

Brought back nightmares of dreaming up excuses for teacher explaining why I couldn't do algebra homework!!

Great OP got a result.

getmore4less

now you have the basic calcs you can set up a sheet that updates every time you add a win or loss.

You could do it so that you you take the say the previous 100/200/300 win rates and get a new target for each one.
the longer you play the harder it will get to improve the long running average and statistically you won't see the real trends, eg 100% win rate won't change the long running average much.

you could say do a latest n00 against the latest y000  
Another analysis is a rolling win rate to see if you go through bad patches and look for other trends.

WaywardDriver

As previously said could use Excel Goal Seek (under Data-What If Analysis) shown below with formulae.

Starting with 999 as a rough guess at the answer, Goal Seek changes this to achieve target of 54% in H2. The answer is 1593. But it's better to use an exact mathematical answer where it exists as per DebitCardMayhem. This is shown by the formulae in B4:B6 for various targets. The exact answer for 54% is 1606.

Stuck_1963

getmore4less said:

now you have the basic calcs you can set up a sheet that updates every time you add a win or loss.

You could do it so that you you take the say the previous 100/200/300 win rates and get a new target for each one.
the longer you play the harder it will get to improve the long running average and statistically you won't see the real trends, eg 100% win rate won't change the long running average much.

you could say do a latest n00 against the latest y000  
Another analysis is a rolling win rate to see if you go through bad patches and look for other trends.

Thanks - that's exactly what I am doing.  Not only do I record that 'overall' % win (started from over many, many games ago, a lot of which were poorly played, or ended badly for non-playing reasons) but I also record daily % wins and % wins (rolling wins) ever since I've started playing properly (which is that 59.36%) from around the last 323 games.  

So, yes, my true stat really, is from that 59.36% but as explained previously, as I'm playing from figures off an App, I need to punch their numbers in and let the spreadsheet do the heavy lifting.

My next tweak is to use that same equation to calculate from the 59.36% starting point (the rolling win% from the start of 'playing properly from 323 games ago) and ask the question How many games to get to x% (eg 60%) from that starting point (and pretty much ignore the Overall Win %).

getmore4less

I did something a while back and rather than use a goal seek I used a feedback loop
That way any update of any cell calculates the new answer

Heedtheadvice

Wayward is right about accuracy of result...under default setting.

However this is optional as it can be vastly increased to be much more accurate.

To save me explaining how to do it see https://orion.math.iastate.edu/mathbus/sp/f10/help/Excel.html  as one description that is easy to follow. One setting..much better!