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

#### vindieu

##### New member
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 !

#### JeffM

##### Elite Member
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
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!

#### Subhotosh Khan

##### Super Moderator
Staff member
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
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
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
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.

Last edited:

#### ksdhart2

##### Senior Member
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
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

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

#### vindieu

##### New member
Thank you very much ksdhart2 & JeffM !

#### Denis

##### Senior Member
You DO know that vindieu = wine god in French, right?

#### vindieu

##### New member
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
Just sent you my email. Thanks!!!!

#### JeffM

##### Elite Member
You DO know that vindieu = wine god in French, right?
And what better name might one have

#### vindieu

##### New member
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...

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

Thank you & have a nice day.

#### Denis

##### Senior Member
Merci, je suis français
Moi aussi, tabarnak!!
Franco-Ontarien: half our words are in English anyway!!

#### vindieu

##### New member

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

Thank you & have a nice day.

#### ksdhart2

##### Senior Member

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

Thank you & 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?