Gaming : Win rate and luck (does anybody know how that guy got this table?)

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Hi everyone!

I'm not a math/stat guy at all (I'm a game player), so please be simple, I have a basic brain 😊
Does someone know how this guy got these results? :


"Assuming that you have a true win rate of 50%, this table tells you within what area most players
(95% percent of the to be exact, 2,5 will have higher and 2,5 will have lower) win rates will end up based on luck alone."
https://forum.worldofwarships.eu/topic/76406-win-rate-and-luck/

How can I get the "Win rate area" in Excel ? (see above)
(I would like to enter the # of games (= coin flips) and get the corresponding "Win rate area").
(Ideally, I would also like to be able to enter/change the "95%").

Someone just told me : "it looks like they're using the normal approximation to the binomial distribution to get their numbers though"
Unfortunately, I haven't been able to find out by myself how to recreate the table.

I have tried "=BINOM.DIST(A1/2,A1,0.5,TRUE)" and got these values ?! :

50 0.556137586
100 0.539794619
200 0.52817424
500 0.517832323
1000 0.512612509
3000 0.507283049
5000 0.505641614
10000 0.503989323

Maybe I should try "=BINOM.DIST.RANGE(" but I don't really understand what values I should select... There's also =NORM.DIST( ?!
I have watched video on Youtube about "normal approximation to the binomial distribution" but they were too complicated for my brain to be honest.

Any help will be welcome.
Thanks very much ! :thumbup:
 

JeffM

Elite Member
Joined
Sep 14, 2012
Messages
3,557
I don't know if excel has functions that will do what you want, but it is easy enough to build the probabilities that you want as a table in excel.

If each trial is independent of every other and p is the probability of success on any given trial, then the probability of exactly k successes in n trials is given by the formula

\(\displaystyle \dfrac{n!}{k! * (n - k)!} * p^k * (1 - p)^{(n-k)}.\)

The symbol x! means the factorial of x, and the function in excel that calculates it for you is fact.

So the formula in excel is

=fact(n}/(fact(k)*fact(n-k))*p^k*(1-p)^(n-k)
 

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Thank you Jeff for your help! You seem to be excellent in math!!!!
I tried to include your formula in excel but it seems that I did something wrong and I really don't understand what...
Thanks again!

11606
 

Subhotosh Khan

Super Moderator
Staff member
Joined
Jun 18, 2007
Messages
18,451
Thank you Jeff for your help! You seem to be excellent in math!!!!
I tried to include your formula in excel but it seems that I did something wrong and I really don't understand what...
Thanks again!

View attachment 11606
I suppose one of your numbers getting too big for excel to handle
 

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Thanks Subhotosh, but it seems the "Result column" is incorrect as well...
How can I get the 36.1%-63.9% result for 50 trials??
 

ksdhart2

Senior Member
Joined
Mar 25, 2016
Messages
1,148
I suppose one of your numbers getting too big for excel to handle
That's exactly what's happening. Because of how Excel stores its data, it can only support numbers strictly smaller than \(2^{1024} \approx 1.8 \cdot 10^{308}\). And part of the calculation in row 4 is \(200! \approx 7.88 \cdot 10^{374} \gg 2^{1024}\), so Excel throws an error. To circumvent this, instead of using the factorial try using:
Code:
=COMBIN(A4, B4) * 0.5^A4
Note, however, that I've simplified things a bit by taking advantage of the fact that \(p = 1 - p = 0.5\), which means that \(p^k \cdot (1 - p)^{n - k} = 0.5^k \cdot 0.5^{n - k} = 0.5^n\). This simplification will not work for any generic value of \(p\) and you'll have the use the full form.
 

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Thanks ksdhart2 but I still don't understand why I don't get the 36.1%-63.9% result for 50 trials?? 40.2%-59.8% for 100, etc.

11608
 
Last edited:

ksdhart2

Senior Member
Joined
Mar 25, 2016
Messages
1,148
I'll freely admit that it is a bit obtuse, and I had to look at it and really think deeply about what was going on and how that range was derived. The formula we've been using here gives the exact probability of getting \(k\) wins out of \(n\) games (assuming that each game is essentially a coin flip, a 50-50 deal), but that's not particularly helpful if we want to get that range.

Rather, what we want to look at is the distribution of all possible outcomes of our games. That is given by a binomial distribution, with \(n = (\text{# of games})\) and \(p = 0.5\). When \(n\) gets sufficiently large (for my stats classes we said \(n > 30\), but the larger \(n\) is the tighter the approximation), we can approximate this distribution by a normal distribution (aka "bell curve") instead. Since the normal distribution takes on parameters describing the mean and standard deviation, we need to calculate those now. Luckily for us, the mean (\(\mu\)) and standard deviation (\(\sigma\)) of a binomial random variable are easy to calculate. Namely, they are \(\mu = np\) and \(\sigma^2 = np(1-p)\) respectively.

Now we want to look at what range of wins that we expect 95% of players to have. As he mentions in the thread, this means that 2.5% of players will be outliers in either direction. We can then use a z-table to find the z-score corresponding to \(\frac{0.05}{2} = 0.025\). This tells us that we expect 95% of players will be within approximately 1.96 standard deviations of the mean.

Using the first row, where \(n = 50\), as an example, we have \(\mu = 0.5(50) = 25\) and \(\sigma = \sqrt{0.5(1 - 0.5)(50)} = \sqrt{12.5}\). Based on this, we can say that we expect 95% of players who have played exactly 50 games to have somewhere between \(25 - 1.96\sqrt{12.5}\) and \(25 + 1.96\sqrt{12.5}\) wins. Converting this to win rates by dividing by \(n\), we expect 95% of players who have played exactly 50 games to have a win rate in the interval:

\(\displaystyle \left( \frac{25 - 1.96\sqrt{12.5}}{50}, \: \frac{25 + 1.96\sqrt{12.5}}{50} \right)\)

\(\displaystyle \left( 0.361407 \dots, \: 0.638593 \dots \right)\)
 

JeffM

Elite Member
Joined
Sep 14, 2012
Messages
3,557
Thank you Jeff for your help! You seem to be excellent in math!!!!
I tried to include your formula in excel but it seems that I did something wrong and I really don't understand what...
Thanks again!

View attachment 11606
I think you misunderstood my answer. Send me your email address by private PM, and I shall send you the excel spread sheet for any number up to 1000 trials.

As ksdhart says you have to use the binomial distribution for an exact answer.
 

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Thank you very much ksdhart2 & JeffM !
 

Denis

Senior Member
Joined
Feb 17, 2004
Messages
1,724
You DO know that vindieu = wine god in French, right?
 

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Merci, je suis français :)

Back in time, blasphemy was illegal. So when it came to swearing using religious words, people would modify the actual words of religious swearing by different words that however sounded the same in order to avoid their swearing to displease religion.
The origin of Vindiou is the swearing Sacré vain Dieu, which means Holy vain god. In order to avoid blasphemy, the words were changed to Sacrés vingt Dieux which means Holy twenty gods and therefore didn't refer to the christian god while being pronounced the same. It still can mean Holy vain god from a phonetical point of view.
Still in order to avoid blasphemy, Sacré vingt Dieu was contracted to Crévindieu or Vindieu. For the same purpose, it was often pronounced Vindiou instead of Vindieu so that the words Dieu (god) was technically not heard in that word and didn't sound like blasphemy while actually being blasphemy.
The meaning of that swearing is close to ****, Holy cow or Holy **** in english. Nothing do to with racism.
  • Vindiou ! Je suis crevé ! (****, I'm so tired !)
  • Vindiou, quelle journée ! (Holy cow, what a day !)
Note that it is a very rare expression in today's french. It is often used to mock the way stereotypical peasants would talk. Indeed the contraction of Dieu into Diou sounds very rural.
 

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Send me your email address by private PM, and I shall send you the excel spread sheet for any number up to 1000 trials.
Just sent you my email. Thanks!!!!
 

JeffM

Elite Member
Joined
Sep 14, 2012
Messages
3,557

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Hi,

I don't understand what happened to Jeff : He told me he would send me a spreadsheet by Monday, we're on Friday, he didn't send me anything and for some reason, he doesn't reply to my private messages ?!

I hope nothing bad happened to him... :unsure:

ksdhart, you found the solution. I would appreciate if you could help me with Excel.

Thank you :thumbup: & have a nice day.
 

Denis

Senior Member
Joined
Feb 17, 2004
Messages
1,724

vindieu

New member
Joined
Mar 31, 2019
Messages
11
Denis, please PM for off topic messages, they spoil the thread.

ksdhart, you found the solution. I would appreciate if you could help me with Excel.

Thank you :thumbup: & have a nice day.
 

ksdhart2

Senior Member
Joined
Mar 25, 2016
Messages
1,148
Denis, please PM for off topic messages, they spoil the thread.

ksdhart, you found the solution. I would appreciate if you could help me with Excel.

Thank you :thumbup: & have a nice day.
I think I probably can. Unfortunately, I've been very busy with work right now. But I should have some time to work on it this afternoon. I assume you're after a formula which outputs the contents of the "Win Rate Area" cell?
 

Denis

Senior Member
Joined
Feb 17, 2004
Messages
1,724

vindieu

New member
Joined
Mar 31, 2019
Messages
11
I assume you're after a formula which outputs the contents of the "Win Rate Area" cell?
Yes ksdhard2!! Thank you very much! Is it possible up to 100,000 trials??
True. So why did you make post#12?
I shouldn't have, I just tried to be polite.
 
Top