Calculate the probability of each number in 3 columns

B

bilbon

Board Regular
Joined
Dec 19, 2011
Messages
83
Hi,

Is there anyone who can help me figure out the probability of each number on the different A-B-C so that it will be like the answer to the right.
Preferably with VBA.


[TABLE="width: 1096"]
<colgroup><col span="5"><col span="3"><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Match[/TD]
[TD]%[/TD]
[TD]%[/TD]
[TD]%[/TD]
[TD][/TD]
[TD]Probability % A[/TD]
[TD]Probability % B[/TD]
[TD]Probability % C[/TD]
[TD][/TD]
[TD]Number of Right[/TD]
[TD]Probability % A[/TD]
[TD]Probability % B[/TD]
[TD]Probability % C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14[/TD]
[TD]25[/TD]
[TD]61[/TD]
[TD][/TD]
[TD]61[/TD]
[TD]25[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]0,06[/TD]
[TD]0,00[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]92[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]92[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]0,60[/TD]
[TD]0,00[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]49[/TD]
[TD]30[/TD]
[TD]21[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]30[/TD]
[TD]21[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]2,86[/TD]
[TD]0,00[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50[/TD]
[TD]27[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]27[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]8,26[/TD]
[TD]0,01[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]69[/TD]
[TD]20[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]69[/TD]
[TD]20[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]16,01[/TD]
[TD]0,07[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]48[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]48[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]21,93[/TD]
[TD]0,39[/TD]
[TD]0,04[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]48[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]48[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]21,80[/TD]
[TD]1,66[/TD]
[TD]0,26[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]49[/TD]
[TD]28[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]28[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]15,87[/TD]
[TD]5,24[/TD]
[TD]1,29[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]56[/TD]
[TD]25[/TD]
[TD]19[/TD]
[TD][/TD]
[TD]56[/TD]
[TD]25[/TD]
[TD]19[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8,42[/TD]
[TD]12,25[/TD]
[TD]4,70[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]28[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3,20[/TD]
[TD]20,95[/TD]
[TD]12,39[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]64[/TD]
[TD]22[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]64[/TD]
[TD]22[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]0,84[/TD]
[TD]25,54[/TD]
[TD]23,12[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]67[/TD]
[TD]19[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]67[/TD]
[TD]19[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]0,14[/TD]
[TD]21,02[/TD]
[TD]28,93[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]19[/TD]
[TD]30[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]51[/TD]
[TD]30[/TD]
[TD]19[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0,01[/TD]
[TD]10,48[/TD]
[TD]21,79[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0,00[/TD]
[TD]2,39[/TD]
[TD]7,47[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]This is the correct answer[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
bilbon said:
Hi Kishan


I make an attempt to explain a little.
The game is to predict 1, X or 2 (winner, tie, away win) in 13 football matches,
I use it, among other things, to make a theoretical assessment of how the pros (those who set the odds) assess the weekly round.
Then there are many ways to limit the number of rows (totaling 1 594 323 rows) by setting filters to remove rows that you don't want to include in their system using these % numbers.
Just your own imagination that sets the limit.

/Bilbon
Click to expand...
Bilbon, thank you for the replaying, I understood that the game is to predict 1, X or 2 (winner, tie, away win) in 13 football matches, also that the number of totalling rows will be 3x3x3x3x3x3x3x3x3x3x3x3x3 = 1.594.323 </SPAN></SPAN>

What I do not understand is following looking to the solution is provided by shg in to the post#9 </SPAN></SPAN>

Take an example for </SPAN></SPAN>
Match 1 according to % (1 has 61%) but according to Probability will you play 2?</SPAN></SPAN>
Match 2 according to % (1 has 92%) but according to Probability will you play 2?</SPAN></SPAN>
Match 3 according to % (1 has 49%) but according to Probability will you play 2?</SPAN></SPAN>
Match 4 according to % (1 has 50%) but according to Probability will you play 2?</SPAN></SPAN>

[TABLE="width: 601"]
<TBODY>[TR]
[TD]Match</SPAN>
[/TD]
[TD][/TD]
[TD]1</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD]k</SPAN>
[/TD]
[TD]Pr(k)</SPAN>
[/TD]
[TD]Pr(k)</SPAN>
[/TD]
[TD]Pr(k)</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD][/TD]
[TD]61%</SPAN>
[/TD]
[TD]25%</SPAN>
[/TD]
[TD]14%</SPAN>
[/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0,000005</SPAN>
[/TD]
[TD]0,023946</SPAN>
[/TD]
[TD]0,074681</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD]92%</SPAN>
[/TD]
[TD]6%</SPAN>
[/TD]
[TD]2%</SPAN>
[/TD]
[TD][/TD]
[TD]1</SPAN>
[/TD]
[TD]0,000134</SPAN>
[/TD]
[TD]0,104848</SPAN>
[/TD]
[TD]0,217900</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD]49%</SPAN>
[/TD]
[TD]30%</SPAN>
[/TD]
[TD]21%</SPAN>
[/TD]
[TD][/TD]
[TD]2</SPAN>
[/TD]
[TD]0,001417</SPAN>
[/TD]
[TD]0,210233</SPAN>
[/TD]
[TD]0,289332</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD]50%</SPAN>
[/TD]
[TD]27%</SPAN>
[/TD]
[TD]23%</SPAN>
[/TD]
[TD][/TD]
[TD]3</SPAN>
[/TD]
[TD]0,008399</SPAN>
[/TD]
[TD]0,255402</SPAN>
[/TD]
[TD]0,231244</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


I could not understand how you reduce the odd using the Probability do have you any software which do reduce the lines?</SPAN></SPAN>

Please will be great full of anyone can understand and help about the Probability % </SPAN></SPAN>

Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi Kishan


Each % number is a character 1 X 2 and if I make a row with the 13 biggest % numbers I can see on the prediction table how many right it should be in that row. Then I do the same with the second largest and the same with the smallest. These 3 lines I put into a program and put the conditions on each line (6-9 right.1-5 right.1-4 right) which I colored. These 3 rows of the terms I set reduces the 1 594 323 lines to 300 872 rows.
This can be a basis when you are going to reduce but then you can continue with several conditions to get the radtal you want to play.


[TABLE="width: 512"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Match[/TD]
[TD]%[/TD]
[TD]%[/TD]
[TD]%[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14[/TD]
[TD]25[/TD]
[TD]61[/TD]
[TD][/TD]
[TD]61%[/TD]
[TD]25%[/TD]
[TD]14%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]92[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]92%[/TD]
[TD]6%[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]49[/TD]
[TD]30[/TD]
[TD]21[/TD]
[TD][/TD]
[TD]49%[/TD]
[TD]30%[/TD]
[TD]21%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50[/TD]
[TD]27[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]50%[/TD]
[TD]27%[/TD]
[TD]23%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]69[/TD]
[TD]20[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]69%[/TD]
[TD]20%[/TD]
[TD]11%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]48[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]48%[/TD]
[TD]30%[/TD]
[TD]22%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]48[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]48%[/TD]
[TD]30%[/TD]
[TD]22%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]49[/TD]
[TD]28[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]49%[/TD]
[TD]28%[/TD]
[TD]23%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]56[/TD]
[TD]25[/TD]
[TD]19[/TD]
[TD][/TD]
[TD]56%[/TD]
[TD]25%[/TD]
[TD]19%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD][/TD]
[TD]43%[/TD]
[TD]29%[/TD]
[TD]28%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]64[/TD]
[TD]22[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]64%[/TD]
[TD]22%[/TD]
[TD]14%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]67[/TD]
[TD]19[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]67%[/TD]
[TD]19%[/TD]
[TD]14%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]19[/TD]
[TD]30[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]51%[/TD]
[TD]30%[/TD]
[TD]19%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6-9[/TD]
[TD]1-5[/TD]
[TD]1-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]k[/TD]
[TD]Pr(k)[/TD]
[TD]Pr(k)[/TD]
[TD]Pr(k)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0,00%[/TD]
[TD]2,39%[/TD]
[TD]7,47%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0,01%[/TD]
[TD]10,48%[/TD]
[TD]21,79%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]0,14%[/TD]
[TD]21,02%[/TD]
[TD]28,93%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]0,84%[/TD]
[TD]25,54%[/TD]
[TD]23,12%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3,20%[/TD]
[TD]20,95%[/TD]
[TD]12,39%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8,42%[/TD]
[TD]12,25%[/TD]
[TD]4,70%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]15,87%[/TD]
[TD]5,24%[/TD]
[TD]1,29%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]21,80%[/TD]
[TD]1,66%[/TD]
[TD]0,26%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]21,93%[/TD]
[TD]0,39%[/TD]
[TD]0,04%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]16,01%[/TD]
[TD]0,07%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]8,26%[/TD]
[TD]0,01%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]2,86%[/TD]
[TD]0,00%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]0,60%[/TD]
[TD]0,00%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD]0,06%[/TD]
[TD]0,00%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
bilbon said:
Hi Kishan


Each % number is a character 1 X 2 and if I make a row with the 13 biggest % numbers I can see on the prediction table how many right it should be in that row. Then I do the same with the second largest and the same with the smallest. These 3 lines I put into a program and put the conditions on each line (6-9 right.1-5 right.1-4 right) which I colored. These 3 rows of the terms I set reduces the 1 594 323 lines to 300 872 rows.
This can be a basis when you are going to reduce but then you can continue with several conditions to get the radtal you want to play.
Click to expand...
Hi bilbon, thank you for your </SPAN>patient</SPAN> I am catching a track bit, you are really using a probability % in your programme to eliminate the lines. Can I ask you is it a any free programme? If so please can you provide a link? </SPAN></SPAN>

I know the one but not very familiar with it here is a link you can download it is totally free and reliable too may help you </SPAN></SPAN>
http://www.megaquin1x2.com/</SPAN></SPAN>

Please any other tips I would like to get from you </SPAN></SPAN>

Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>
 
Last edited:
Upvote 0
Hi SHG or anyone else


Would there be much change in the code to reverse the table so 13 comes at the top and 0 at the bottom.
I am not so knowledgeable that I can handle it myself.


/Bilbon
 
Upvote 0
Hi Kishan


The programs I use are not free and do not think the little more advanced programs are free.
What you link to is too difficult for me with Spanish but thanks anyway.


/Bilbon
 
Last edited:
Upvote 0
I'm sure it's possible to change shg's UDF to go in the opposite order, but I'll leave that up to shg. In the meantime, you can use that UDF and get the results you want by just changing the formula on the sheet. In column J, put the k values in the order you want. Delete out the formulas from K2:M15. Then in K2 put this formula:

=INDEX(poissonbinom(F$2:F$14),$J2+1)

copy down and across as needed. Somewhat oddly, column K sums to 100%, where it didn't originally. I'll have to look at that.
 
Upvote 0
Somewhat oddly, column K sums to 100%, where it didn't originally.
Click to expand...
I think I must have had an error in the sum formula :eeek:

I added an option to use either the fast (unstable) method or the slow (more accurate) method. Fast:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][td="bgcolor:#C0C0C0"]
J​
[/td][td="bgcolor:#C0C0C0"]
K​
[/td][td="bgcolor:#C0C0C0"]
L​
[/td][td="bgcolor:#C0C0C0"]
M​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Fast​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
TRUE​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Sorted​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td][/td][td="bgcolor:#F3F3F3"]
k​
[/td][td="bgcolor:#F3F3F3"]
Pr(k)​
[/td][td="bgcolor:#F3F3F3"]
Pr(k)​
[/td][td="bgcolor:#F3F3F3"]
Pr(k)​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td="bgcolor:#F3F3F3"]
Match​
[/td][td="bgcolor:#F3F3F3"]
Win​
[/td][td="bgcolor:#F3F3F3"]
Draw​
[/td][td="bgcolor:#F3F3F3"]
Lose​
[/td][td][/td][td="bgcolor:#F3F3F3"]
1​
[/td][td="bgcolor:#F3F3F3"]
2​
[/td][td="bgcolor:#F3F3F3"]
3​
[/td][td][/td][td="bgcolor:#E5E5E5"]
0​
[/td][td="bgcolor:#CCFFFF"]
0.000 004 965 618 890​
[/td][td="bgcolor:#CCFFFF"]
0.023 945 607 207 981​
[/td][td="bgcolor:#CCFFFF"]
0.074 681 273 868 039​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td="bgcolor:#E5E5E5"]
1​
[/td][td]
14%​
[/td][td]
25%​
[/td][td]
61%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
61%​
[/td][td="bgcolor:#E5E5E5"]
25%​
[/td][td="bgcolor:#E5E5E5"]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
1​
[/td][td="bgcolor:#CCFFFF"]
0.000 133 742 200 799​
[/td][td="bgcolor:#CCFFFF"]
0.104 848 330 504 405​
[/td][td="bgcolor:#CCFFFF"]
0.217 899 673 681 578​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td="bgcolor:#E5E5E5"]
2​
[/td][td]
92%​
[/td][td]
6%​
[/td][td]
2%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
92%​
[/td][td="bgcolor:#E5E5E5"]
6%​
[/td][td="bgcolor:#E5E5E5"]
2%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
2​
[/td][td="bgcolor:#CCFFFF"]
0.001 416 853 003 021​
[/td][td="bgcolor:#CCFFFF"]
0.210 233 356 636 269​
[/td][td="bgcolor:#CCFFFF"]
0.289 332 167 783 446​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td="bgcolor:#E5E5E5"]
3​
[/td][td]
49%​
[/td][td]
30%​
[/td][td]
21%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
49%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
21%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
3​
[/td][td="bgcolor:#CCFFFF"]
0.008 399 492 726 107​
[/td][td="bgcolor:#CCFFFF"]
0.255 402 445 312 009​
[/td][td="bgcolor:#CCFFFF"]
0.231 243 609 312 226​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td="bgcolor:#E5E5E5"]
4​
[/td][td]
50%​
[/td][td]
27%​
[/td][td]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
50%​
[/td][td="bgcolor:#E5E5E5"]
27%​
[/td][td="bgcolor:#E5E5E5"]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
4​
[/td][td="bgcolor:#CCFFFF"]
0.032 028 692 277 197​
[/td][td="bgcolor:#CCFFFF"]
0.209 544 750 787 826​
[/td][td="bgcolor:#CCFFFF"]
0.123 943 684 241 114​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td="bgcolor:#E5E5E5"]
5​
[/td][td]
69%​
[/td][td]
20%​
[/td][td]
11%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
69%​
[/td][td="bgcolor:#E5E5E5"]
20%​
[/td][td="bgcolor:#E5E5E5"]
11%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
5​
[/td][td="bgcolor:#CCFFFF"]
0.084 242 187 404 515​
[/td][td="bgcolor:#CCFFFF"]
0.122 468 272 545 888​
[/td][td="bgcolor:#CCFFFF"]
0.046 962 991 654 853​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td="bgcolor:#E5E5E5"]
6​
[/td][td]
48%​
[/td][td]
30%​
[/td][td]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
48%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
6​
[/td][td="bgcolor:#CCFFFF"]
0.158 674 700 986 833​
[/td][td="bgcolor:#CCFFFF"]
0.052 380 939 169 867​
[/td][td="bgcolor:#CCFFFF"]
0.012 911 869 631 450​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td="bgcolor:#E5E5E5"]
7​
[/td][td]
48%​
[/td][td]
30%​
[/td][td]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
48%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
7​
[/td][td="bgcolor:#CCFFFF"]
0.217 987 541 603 344​
[/td][td="bgcolor:#CCFFFF"]
0.016 569 626 657 441​
[/td][td="bgcolor:#CCFFFF"]
0.002 599 892 845 129​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td="bgcolor:#E5E5E5"]
8​
[/td][td]
49%​
[/td][td]
28%​
[/td][td]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
49%​
[/td][td="bgcolor:#E5E5E5"]
28%​
[/td][td="bgcolor:#E5E5E5"]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
8​
[/td][td="bgcolor:#CCFFFF"]
0.219 323 229 058 714​
[/td][td="bgcolor:#CCFFFF"]
0.003 866 932 009 929​
[/td][td="bgcolor:#CCFFFF"]
0.000 381 709 007 839​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td="bgcolor:#E5E5E5"]
9​
[/td][td]
56%​
[/td][td]
25%​
[/td][td]
19%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
56%​
[/td][td="bgcolor:#E5E5E5"]
25%​
[/td][td="bgcolor:#E5E5E5"]
19%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
9​
[/td][td="bgcolor:#CCFFFF"]
0.160 095 690 495 266​
[/td][td="bgcolor:#CCFFFF"]
0.000 655 322 787 878​
[/td][td="bgcolor:#CCFFFF"]
0.000 040 092 829 492​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td="bgcolor:#E5E5E5"]
10​
[/td][td]
28%​
[/td][td]
29%​
[/td][td]
43%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
43%​
[/td][td="bgcolor:#E5E5E5"]
29%​
[/td][td="bgcolor:#E5E5E5"]
28%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
10​
[/td][td="bgcolor:#CCFFFF"]
0.082 600 611 102 899​
[/td][td="bgcolor:#CCFFFF"]
0.000 078 001 030 912​
[/td][td="bgcolor:#CCFFFF"]
0.000 002 897 732 972​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td="bgcolor:#E5E5E5"]
11​
[/td][td]
64%​
[/td][td]
22%​
[/td][td]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
64%​
[/td][td="bgcolor:#E5E5E5"]
22%​
[/td][td="bgcolor:#E5E5E5"]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
11​
[/td][td="bgcolor:#CCFFFF"]
0.028 577 327 922 008​
[/td][td="bgcolor:#CCFFFF"]
0.000 006 128 456 875​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 133 961 486​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td="bgcolor:#E5E5E5"]
12​
[/td][td]
67%​
[/td][td]
19%​
[/td][td]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
67%​
[/td][td="bgcolor:#E5E5E5"]
19%​
[/td][td="bgcolor:#E5E5E5"]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
12​
[/td][td="bgcolor:#CCFFFF"]
0.005 950 949 314 411​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 281 325 450​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 003 417 567​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td="bgcolor:#E5E5E5"]
13​
[/td][td]
19%​
[/td][td]
30%​
[/td][td]
51%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
51%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
19%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
13​
[/td][td="bgcolor:#CCFFFF"]
0.000 564 015 896 089​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 005 567 271​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 000 032 809​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Total​
[/td][td="bgcolor:#E5E5E5"]
0.999 999 999 610 092​
[/td][td="bgcolor:#E5E5E5"]
1.000 000 000 000 000​
[/td][td="bgcolor:#E5E5E5"]
1.000 000 000 000 000​
[/td][/tr]
[/table]

Formula in K4:K17: =TRANSPOSE(PoissonBinom(F5:F17, $K$2))

Slow:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][td="bgcolor:#C0C0C0"]
J​
[/td][td="bgcolor:#C0C0C0"]
K​
[/td][td="bgcolor:#C0C0C0"]
L​
[/td][td="bgcolor:#C0C0C0"]
M​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Fast​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
FALSE​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Sorted​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td][/td][td="bgcolor:#F3F3F3"]
k​
[/td][td="bgcolor:#F3F3F3"]
Pr(k)​
[/td][td="bgcolor:#F3F3F3"]
Pr(k)​
[/td][td="bgcolor:#F3F3F3"]
Pr(k)​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td="bgcolor:#F3F3F3"]
Match​
[/td][td="bgcolor:#F3F3F3"]
Win​
[/td][td="bgcolor:#F3F3F3"]
Draw​
[/td][td="bgcolor:#F3F3F3"]
Lose​
[/td][td][/td][td="bgcolor:#F3F3F3"]
1​
[/td][td="bgcolor:#F3F3F3"]
2​
[/td][td="bgcolor:#F3F3F3"]
3​
[/td][td][/td][td="bgcolor:#E5E5E5"]
0​
[/td][td="bgcolor:#CCFFFF"]
0.000 004 965 618 890​
[/td][td="bgcolor:#CCFFFF"]
0.023 945 607 207 981​
[/td][td="bgcolor:#CCFFFF"]
0.074 681 273 868 039​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td="bgcolor:#E5E5E5"]
1​
[/td][td]
14%​
[/td][td]
25%​
[/td][td]
61%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
61%​
[/td][td="bgcolor:#E5E5E5"]
25%​
[/td][td="bgcolor:#E5E5E5"]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
1​
[/td][td="bgcolor:#CCFFFF"]
0.000 133 742 200 799​
[/td][td="bgcolor:#CCFFFF"]
0.104 848 330 504 405​
[/td][td="bgcolor:#CCFFFF"]
0.217 899 673 681 578​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td="bgcolor:#E5E5E5"]
2​
[/td][td]
92%​
[/td][td]
6%​
[/td][td]
2%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
92%​
[/td][td="bgcolor:#E5E5E5"]
6%​
[/td][td="bgcolor:#E5E5E5"]
2%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
2​
[/td][td="bgcolor:#CCFFFF"]
0.001 416 853 003 021​
[/td][td="bgcolor:#CCFFFF"]
0.210 233 356 636 269​
[/td][td="bgcolor:#CCFFFF"]
0.289 332 167 783 446​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td="bgcolor:#E5E5E5"]
3​
[/td][td]
49%​
[/td][td]
30%​
[/td][td]
21%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
49%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
21%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
3​
[/td][td="bgcolor:#CCFFFF"]
0.008 399 492 726 107​
[/td][td="bgcolor:#CCFFFF"]
0.255 402 445 312 009​
[/td][td="bgcolor:#CCFFFF"]
0.231 243 609 312 226​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td="bgcolor:#E5E5E5"]
4​
[/td][td]
50%​
[/td][td]
27%​
[/td][td]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
50%​
[/td][td="bgcolor:#E5E5E5"]
27%​
[/td][td="bgcolor:#E5E5E5"]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
4​
[/td][td="bgcolor:#CCFFFF"]
0.032 028 692 277 197​
[/td][td="bgcolor:#CCFFFF"]
0.209 544 750 787 826​
[/td][td="bgcolor:#CCFFFF"]
0.123 943 684 241 114​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td="bgcolor:#E5E5E5"]
5​
[/td][td]
69%​
[/td][td]
20%​
[/td][td]
11%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
69%​
[/td][td="bgcolor:#E5E5E5"]
20%​
[/td][td="bgcolor:#E5E5E5"]
11%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
5​
[/td][td="bgcolor:#CCFFFF"]
0.084 242 187 404 516​
[/td][td="bgcolor:#CCFFFF"]
0.122 468 272 545 888​
[/td][td="bgcolor:#CCFFFF"]
0.046 962 991 654 853​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td="bgcolor:#E5E5E5"]
6​
[/td][td]
48%​
[/td][td]
30%​
[/td][td]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
48%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
6​
[/td][td="bgcolor:#CCFFFF"]
0.158 674 700 986 832​
[/td][td="bgcolor:#CCFFFF"]
0.052 380 939 169 867​
[/td][td="bgcolor:#CCFFFF"]
0.012 911 869 631 450​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td="bgcolor:#E5E5E5"]
7​
[/td][td]
48%​
[/td][td]
30%​
[/td][td]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
48%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
22%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
7​
[/td][td="bgcolor:#CCFFFF"]
0.217 987 541 603 338​
[/td][td="bgcolor:#CCFFFF"]
0.016 569 626 657 441​
[/td][td="bgcolor:#CCFFFF"]
0.002 599 892 845 129​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td="bgcolor:#E5E5E5"]
8​
[/td][td]
49%​
[/td][td]
28%​
[/td][td]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
49%​
[/td][td="bgcolor:#E5E5E5"]
28%​
[/td][td="bgcolor:#E5E5E5"]
23%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
8​
[/td][td="bgcolor:#CCFFFF"]
0.219 323 229 058 589​
[/td][td="bgcolor:#CCFFFF"]
0.003 866 932 009 929​
[/td][td="bgcolor:#CCFFFF"]
0.000 381 709 007 839​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td="bgcolor:#E5E5E5"]
9​
[/td][td]
56%​
[/td][td]
25%​
[/td][td]
19%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
56%​
[/td][td="bgcolor:#E5E5E5"]
25%​
[/td][td="bgcolor:#E5E5E5"]
19%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
9​
[/td][td="bgcolor:#CCFFFF"]
0.160 095 690 494 111​
[/td][td="bgcolor:#CCFFFF"]
0.000 655 322 787 878​
[/td][td="bgcolor:#CCFFFF"]
0.000 040 092 829 492​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td="bgcolor:#E5E5E5"]
10​
[/td][td]
28%​
[/td][td]
29%​
[/td][td]
43%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
43%​
[/td][td="bgcolor:#E5E5E5"]
29%​
[/td][td="bgcolor:#E5E5E5"]
28%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
10​
[/td][td="bgcolor:#CCFFFF"]
0.082 600 611 084 913​
[/td][td="bgcolor:#CCFFFF"]
0.000 078 001 030 912​
[/td][td="bgcolor:#CCFFFF"]
0.000 002 897 732 972​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td="bgcolor:#E5E5E5"]
11​
[/td][td]
64%​
[/td][td]
22%​
[/td][td]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
64%​
[/td][td="bgcolor:#E5E5E5"]
22%​
[/td][td="bgcolor:#E5E5E5"]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
11​
[/td][td="bgcolor:#CCFFFF"]
0.028 577 327 748 556​
[/td][td="bgcolor:#CCFFFF"]
0.000 006 128 456 875​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 133 961 486​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td="bgcolor:#E5E5E5"]
12​
[/td][td]
67%​
[/td][td]
19%​
[/td][td]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
67%​
[/td][td="bgcolor:#E5E5E5"]
19%​
[/td][td="bgcolor:#E5E5E5"]
14%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
12​
[/td][td="bgcolor:#CCFFFF"]
0.005 950 947 970 558​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 281 325 450​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 003 417 567​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td="bgcolor:#E5E5E5"]
13​
[/td][td]
19%​
[/td][td]
30%​
[/td][td]
51%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
51%​
[/td][td="bgcolor:#E5E5E5"]
30%​
[/td][td="bgcolor:#E5E5E5"]
19%​
[/td][td][/td][td="bgcolor:#E5E5E5"]
13​
[/td][td="bgcolor:#CCFFFF"]
0.000 564 017 822 574​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 005 567 271​
[/td][td="bgcolor:#CCFFFF"]
0.000 000 000 032 809​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Total​
[/td][td="bgcolor:#E5E5E5"]
1.000 000 000 000 000​
[/td][td="bgcolor:#E5E5E5"]
1.000 000 000 000 000​
[/td][td="bgcolor:#E5E5E5"]
1.000 000 000 000 000​
[/td][/tr]
[/table]

Revised code:

Code: 
Function PoissonBinom(rProb As Range, Optional bFast As Boolean = True) As Variant
  ' shg 2018
  ' UDF wrapper for adPoissonBinom

  Dim p()           As Double
  Dim cell          As Range
  Dim i             As Long

  With rProb
    ReDim p(1 To .Cells.Count)
    For Each cell In .Cells
      If VarType(cell.Value2) = vbDouble Then
        If cell.Value2 > 0# And cell.Value2 < 1# Then
          i = i + 1
          p(i) = cell.Value2
        Else
          PoissonBinom = "0 < p < 1! " & cell.Address(False, False)
          Exit Function
        End If
      Else
        PoissonBinom = "Non-numeric: " & cell.Address(False, False)
        Exit Function
      End If
    Next cell
  End With

  If bFast Then PoissonBinom = adPoissonBinom2(p) Else PoissonBinom = adPoissonBinom1(p)
End Function

Private Function adPoissonBinom1(p() As Double) As Double()
  ' shg 2018
  ' VBA only

  ' https://en.wikipedia.org/wiki/Poisson_binomial_distribution#Probability_mass_function
  ' the slow, more accurate method

  Dim n             As Long
  Dim Pr()          As Double
  Dim i             As Long
  Dim j             As Long
  Dim k             As Long
  Dim aiC()         As Long
  Dim A()           As Boolean

  n = UBound(p)

  ReDim Pr(0 To n)
  ReDim T(1 To n)

  Pr(0) = 1#

  For i = 1 To n
    Pr(0) = Pr(0) * (1# - p(i))
  Next i

  For k = 1 To n
    ReDim aiC(1 To k)
    aiC(1) = -1

    Do While bNextCombo(aiC, n)
      ReDim A(1 To n)
      For i = 1 To k
        A(aiC(i) + 1) = True
      Next i

      Dim d         As Double
      d = 1#
      For i = 1 To n
        If A(i) Then d = d * p(i) Else d = d * (1 - p(i))
      Next i
      Pr(k) = Pr(k) + d
    Loop
  Next k

  adPoissonBinom1 = Pr
End Function

Private Function adPoissonBinom2(p() As Double) As Double()
  ' shg 2018
  ' VBA only

  ' https://en.wikipedia.org/wiki/Poisson_binomial_distribution#Probability_mass_function
  ' the fast, lesss numerically stable method

  Dim n             As Long
  Dim Pr()          As Double
  Dim T()           As Double
  Dim i             As Long
  Dim j             As Long
  Dim k             As Long

  n = UBound(p)

  ReDim Pr(0 To n)
  ReDim T(1 To n)

  Pr(0) = 1#

  For i = 1 To n
    Pr(0) = Pr(0) * (1# - p(i))
    For j = 1 To n
      T(i) = T(i) + (p(j) / (1# - p(j))) ^ i
    Next j
  Next i

  For k = 1 To n
    For i = 1 To k
      Pr(k) = Pr(k) + (-1) ^ (i - 1) * Pr(k - i) * T(i) / k
    Next i
  Next k

  adPoissonBinom2 = Pr
End Function

The slow routine requires this:

Code: 
Public Function bNextCombo(aiC() As Long, n As Long) As Boolean
  ' shg 2009-12
  '     2011-07 (modified to require aiC(1) < 0 to initialize)

  ' VBA only

  ' Sets aiC to the next combination of n choose m in lexical order
  ' Returns True unless the combination is the last, in which case
  ' it leaves aiC unmodified.

  ' If aiC(1) < 0, initializes aiC to the first combo:
  '                   {m-1,  m-2, ...,     1,   0}
  ' The last combo is {n-1,  n-2, ..., n-m+1, n-m}

  Dim m             As Long
  Dim i             As Long

  m = UBound(aiC)
  If n < m Then Exit Function

  If aiC(1) < 0 Then    ' set initial combo
    i = 1
    aiC(1) = m - 2

  Else

    ' find rightmost incrementable index
    For i = m To 2 Step -1
      If aiC(i) < aiC(i - 1) - 1 Then Exit For
    Next i
  End If

  If i <> 1 Or aiC(1) < n - 1 Then
    ' increment that index, and set 'righter' indices descending to 0
    aiC(i) = aiC(i) + 1
    For i = i + 1 To m
      aiC(i) = m - i
    Next i

    bNextCombo = True
  End If
End Function
 
Last edited:
Upvote 0
H, Eric and shg


Thanks to both of you for your help, I am so grateful, your suggestions work great

/Bilbon
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
301
Spaceballs_the_fanboy
S
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements
L
  • Solved
Send specific data from one sheet to another when a condition is met,
Replies
7
Views
284
mumps
mumps
K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K
H
  • Solved
Retrieve the intersection of multiple cells with respect to columns and rows
2
Replies
10
Views
164
mumps
mumps

Forum statistics

Threads
1,224,749
Messages
6,180,727
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top