Using the NORMDIST function to find probabilities

[SOBEted]

So, I have the per game average of four bowlers and their per game standard deviations:

John - Avg. 200; SD 15 (variance 225)
Paul - Avg. 180; SD 16 (variance 256)
George - Avg. 175; SD 18 (variance 324)
Ringo - Avg. 170; SD 18 (variance 324)

I want to know the probabilities of each one winning the match. Can I use the NORMDIST function in excel for this problem?

I can use it for two players: =1-NORMDIST(0, the mean difference between the two players averages, add the players total variance and take sqrt. to get the Stand deviation, TRUE)

I just don't know how to use this function for more than 2 players.

Thank You.

 

[KRice]

Interesting question. One approach is to perform a Monte Carlo simulation, as shown in the small example below. The inputs are in the blue cells, and the results of the match simulations are shown in the green cells. The simulations rely on generating a uniformly distributed random number, and then feeding that number into the NORMINV function to generate a normally distributed bowling score given the player's mean and standard deviation. This is done for each player for each match, and the maximum value is then determined and awarded the victory. The results block simply counts up the number of victories for each player and divides by the number of matches for a win percentage. RAND is a volatile function, so the sheet will regularly update each time edits are made or F9 is hit. You can click on the clipboard icon in the upper left (intersection of row and column labels) to copy this for easy pasting into cell A1 of a blank worksheet . You should consider extending the number of matches down the sheet to perform several hundred simulations to develop a better idea of the result statistics...just update the cell references to reflect the total range of the simulation table.

MrExcel20200915.xlsx
	A	B	C	D	E	F
1	Inputs:
2		John	Paul	George	Ringo
3	mean	200	180	175	170
4	stdev	15	16	18	18
5	Wins	65.00%	15.00%	5.00%	15.00%
6
7	Monte Carlo Simulation
8	Match	John	Paul	George	Ringo	Winner
9	1	194.0615	170.8334	188.2741	177.5638	John
10	2	185.3426	162.938	174.9123	179.0674	John
11	3	222.805	165.3655	184.8277	167.4205	John
12	4	167.9046	180.1075	152.8452	134.6718	Paul
13	5	193.1725	176.1163	169.703	178.215	John
14	6	202.1282	174.6725	163.9611	162.7351	John
15	7	185.8703	160.4463	156.543	133.217	John
16	8	212.0141	178.5472	177.7501	218.3237	Ringo
17	9	209.0881	162.7962	185.1158	112.6603	John
18	10	213.1237	191.4796	141.38	165.5944	John
19	11	212.2064	174.5744	197.0914	178.9705	John
20	12	202.6534	195.2643	154.6701	176.436	John
21	13	196.7407	179.9188	168.1991	131.4865	John
22	14	189.4268	194.2176	211.2617	188.6526	George
23	15	186.4543	196.7228	191.5353	170.2739	Paul
24	16	216.4188	172.0572	158.0649	173.3918	John
25	17	218.8587	173.9624	167.0046	188.5287	John
26	18	177.7202	173.1233	174.5724	184.8548	Ringo
27	19	206.3816	184.5937	169.8683	206.8357	Ringo
28	20	177.3262	178.5743	155.4256	176.8047	Paul
SOBEted

Cell Formulas
Range	Formula
B5:E5	B5	=COUNTIF($F$9:$F$28,B2)/COUNTA($F$9:$F$28)
B9:E28	B9	=NORMINV(RAND(),B$3,B$4)
F9:F28	F9	=INDEX($B$8:$E$8,MATCH(MAX(B9:E9),B9:E9,0))

 

[SOBEted]

Thanks very much for your reply. The Monte carlo seems to way to go....I was just curious as to whether there was a way to do it numerically.

As an aside....you recommend running a sample of a hundred million trials which is a good idea....but how can I do that in excel where the max number of rows is slightly over a million?

 

[KRice]

You're welcome. I recommend several hundred (may 300 to 500) simulations...possibly a few thousand. As the number of simulations increases, you shouldn't see as much variation in the results. I should have mentioned that this could be tweaked a bit to round the scores to integers, but then you would likely have tie-breaks to deal with.

 

[KRice]

Here is a version with the formula dragged down to row 2050 and the user can input the number of simulations desired in cell A7. If you extend the formulas down the sheet even further, just update the range referred to in the formulas in B5:E5.

MrExcel20200915.xlsx
	A	B	C	D	E	F
1	Inputs:
2		John	Paul	George	Ringo
3	mean	200	180	175	170
4	stdev	15	16	18	18
5	Wins	68.60%	15.00%	10.85%	5.55%
6
7	2000	<-- Number of Monte Carlo Simulations
8	Match	John	Paul	George	Ringo	Winner
9	1	188.4673	170.0593	183.5602	223.6462	Ringo
10	2	202.8221	157.0993	160.4594	205.3343	Ringo
11	3	215.7227	185.3223	205.6771	143.4469	John
12	4	218.3447	180.0098	148.5282	183.4444	John
13	5	231.8294	205.7264	202.9696	183.4953	John
14	6	194.0568	165.5686	161.4678	180.9195	John
15	7	214.949	171.5839	237.5977	180.5136	George
16	8	191.3459	167.696	168.4692	186.3051	John
17	9	210.7211	183.9491	198.0341	202.7349	John
18	10	228.6084	194.552	187.2302	155.7849	John
19	11	231.8158	197.1209	172.8012	135.3089	John
20	12	177.1231	210.3326	193.9309	175.209	Paul
21	13	213.7646	170.2931	202.9143	166.8451	John
22	14	194.5965	173.1876	181.4388	176.163	John
23	15	211.2355	166.459	179.5138	162.3454	John
24	16	197.6484	169.2739	173.671	145.8523	John
25	17	227.9873	187.1679	191.9363	171.8498	John
26	18	172.8017	193.2195	162.9703	152.1474	Paul
27	19	208.9072	190.8163	199.4393	171.0128	John
28	20	199.204	183.6117	189.9304	150.6078	John
29	21	203.0765	160.4606	179.2019	188.1672	John
SOBEted

Cell Formulas
Range	Formula
B5:E5	B5	=COUNTIF($F$9:$F$2050,B2)/$A$7
A9:A29	A9	=IF(ROWS(A$9:A9)<=$A$7,ROWS(A$9:A9),"")
B9:E29	B9	=IF(ISNUMBER($A9),NORMINV(RAND(),B$3,B$4),"")
F9:F29	F9	=IFERROR(INDEX($B$8:$E$8,MATCH(MAX(B9:E9),B9:E9,0)),"")

 

[KRice]

A quick follow-up. I was curious about how many simulation trials would be needed before the results became fairly consistent. My previous suggestion underestimated the number of trials. The bowling score distributions are fairly broad, which means that a larger number of simulation trials are necessary. I pulled the formulas down to row 50008 (to accommodate up to 50000 trials). When the user specifies 40000 to 50000 simulations the results tend to be fairly consistent with each new recalculation (forced with F9)...with John's win percentage varying by about one percentage point, perhaps a little less, and the other bowlers' win percentages varying by a few tenths of a percentage point. You could pull the formulas down even further and update the formula in B5:E5 to capture the total expanded range if you want to narrow these results even further. So in this case, better guidance would be to run several tens of thousands to perhaps a hundred thousand simulations.

MrExcel20200915.xlsx
	A	B	C	D	E	F
1	Inputs:
2		John	Paul	George	Ringo
3	mean	200	180	175	170
4	stdev	15	16	18	18
5	Wins	68.99%	14.00%	10.53%	6.48%
6
7	50000	<-- Number of Monte Carlo Simulations
8	Match	John	Paul	George	Ringo	Winner
9	1	174.5466	171.9341	169.788	206.0567	Ringo
10	2	196.1404	175.1209	192.5911	152.0164	John
11	3	216.6413	206.0277	185.1446	186.9642	John
SOBEted

Cell Formulas
Range	Formula
B5:E5	B5	=COUNTIF($F$9:$F$50008,B2)/$A$7
A9:A11	A9	=IF(ROWS(A$9:A9)<=$A$7,ROWS(A$9:A9),"")
B9:E11	B9	=IF(ISNUMBER($A9),NORMINV(RAND(),B$3,B$4),"")
F9:F11	F9	=IFERROR(INDEX($B$8:$E$8,MATCH(MAX(B9:E9),B9:E9,0)),"")

 

[Sulprobil]

Even with 2,000,000 runs the results are not stable, if I am not mistaken:

MrExcel_Using the NORMDIST function to find probabilities.xlsm
	B	C	D	E
1	69,20%	13,96%	10,38%	6,46%
2	John	Paul	George	Ringo
Tabelle1

Ringo's percentage is 6,45% or 6,46%

My code:

Sub monte()
Dim i As Long, j As Long, k As Long, p As Long, n As Long
Dim d As Double, dmaxi As Double
Dim m(1 To 10) As Double 'mean
Dim s(1 To 10) As Double 'stdev
Dim w(1 To 10) As Long 'wins

Randomize
k = [A7]
j = 2
Do While Not (IsEmpty(Cells(2, j)))
    m(j - 1) = Cells(3, j).Value
    s(j - 1) = Cells(4, j).Value
    j = j + 1
Loop
j = j - 2
For i = 1 To k
    dmaxi = -1E+300
    For p = 1 To j
        d = Rnd(): If d = 0# Then d = 0.5
        d = Application.WorksheetFunction.NormInv(d, m(p), s(p))
        If d > dmaxi Then dmaxi = d: n = p
    Next p
    w(n) = w(n) + 1
Next i
For i = 1 To j
    Cells(1, i + 1) = w(i) / k
Next i
End Sub

 

[Sulprobil]

BTW: With 100,000,000 runs I get:

MrExcel_Using the NORMDIST function to find probabilities.xlsm
	B	C	D	E
1	69,23%	13,94%	10,38%	6,46%
2	John	Paul	George	Ringo
Tabelle1

 

[KRice]

@Sulprobil, thanks for taking this analysis further. You're not mistaken...one of my 50,000-run simulations shown above gave (68.99%, 14.00%, 10.53%, 6.48%). I mentioned that multiple re-runs of the 50k simulation showed that the first bowler's win percentage (John) tended to have a variation no greater than one percentage point, and the others varied by no more than a few tenths of a percentage point from the values shown. Depending on needs, that may or may not have been close enough. The rate at which answers converge is dependent on the number, positions, and shapes of the probability distributions. In this case, it appears that a considerable number of trials are necessary in order to obtain better convergence. Your conversion of these simulations to code is a much better and more practical way to tackle the problem. I appreciate seeing the refined and converged answers.

 

[Sulprobil]

@KRice Thanks, you inspired this.