Calculate the probability of each number in 3 columns

[bilbon]

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]

 

[Gerald Higgins]

Hi, how are these sets of numbers related to each other ?

I can see how you get from the first set to the second set.
For example from
14.....25.....61
to
61.....25.....14

It looks as if you are sequencing them so that the highest number comes first, lowest number comes last.
ALSO, it looks as if you might be able to simplify slightly, because the middle number never seems to change.

Anyway, I can't see how you derive the last three columns of numbers.
Can you explain clearly please ?

 

[bilbon]

Hi Gerald

Yeah it was a really bad post by me, sorry
I will try to explain a little better but am so bad in English so I use Bing translator so be lenient with me.
I start over and put in the last 2 tables that are most important.

[TABLE="width: 759"]
<colgroup><col span="3"><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]% A[/TD]
[TD]% B[/TD]
[TD] % C[/TD]
[TD][/TD]
[TD]Number of Right[/TD]
[TD]Probability % A[/TD]
[TD]Probability % B[/TD]
[TD]Probability % C[/TD]
[/TR]
[TR]
[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]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]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]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]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]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]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]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]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]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]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]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]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]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]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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[TD="colspan: 3"]This is the correct answer


[/TD]
[/TR]
</tbody>[/TABLE]

To obtain the first% number for Probility% A, we take and calculate the proudukten of the 13 numbers in% A as here and get 0,06 (Red)

[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]% A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]61%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]92%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]69%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]56%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]43%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]64%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]51%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]0,06%[/TD]
[/TR]
</tbody>[/TABLE]



To get the second % 's for Probility% A, you have to figure out 13 computations because 1 match is wrong at 12 right and that error can come in 13 different places. The interesting thing is the percentage of the correct sign and the percentage of the error, i.e. the remaining 100%. Where the error is, you should insert the error rate instead (red numbers). Then you calculate the product for all 13 rows and sum them up and get the answer 0.60 (Green). I try to show below.


[TABLE="width: 900"]
<colgroup><col><col span="5"><col span="3"><col span="5"></colgroup><tbody>[TR]
[TD][TABLE="width: 900"]
<colgroup><col><col span="5"><col span="3"><col span="5"></colgroup><tbody>[TR]
[TD]Error %[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[TD]% A[/TD]
[/TR]
[TR]
[TD]39%[/TD]
[TD]39%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[TD]61%[/TD]
[/TR]
[TR]
[TD]8%[/TD]
[TD]92%[/TD]
[TD]8%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[/TR]
[TR]
[TD]51%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]51%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]31%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]31%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[TD]69%[/TD]
[/TR]
[TR]
[TD]52%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]52%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD]52%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]52%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD]51%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]51%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD]44%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]44%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[TD]56%[/TD]
[/TR]
[TR]
[TD]57%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]57%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[TD]43%[/TD]
[/TR]
[TR]
[TD]36%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[TD]36%[/TD]
[TD]64%[/TD]
[TD]64%[/TD]
[/TR]
[TR]
[TD]33%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]67%[/TD]
[TD]33%[/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD]49%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]51%[/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0,04%[/TD]
[TD]0,00%[/TD]
[TD]0,06%[/TD]
[TD]0,06%[/TD]
[TD]0,03%[/TD]
[TD]0,06%[/TD]
[TD]0,06%[/TD]
[TD]0,06%[/TD]
[TD]0,04%[/TD]
[TD]0,07%[/TD]
[TD]0,03%[/TD]
[TD]0,03%[/TD]
[TD]0,05%[/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]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]0,60%[/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]
</tbody>[/TABLE]
[/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][/TD]
[/TR]
</tbody>[/TABLE]

To get the% figure for 11, you may make 78 calculations in the same way but then you get to replace 2 wrongs% and for 10 you get to do 286 computations and replace 3 wrongs% etc.All the different looks like this.

13= 1
12= 13
11= 78
10= 286
9= 715
8= 1287
7= 1716
6= 1716
5= 1287
4= 715
3= 286
2= 78
1= 13
0= 1

/Bilbon

 

[Gerald Higgins]

Sorry, I don't understand this.

I follow it up to the point where you explain how to get to 0,06%, about half way through post #3 .

After that, I'm afraid I just don't understand you, sorry.

 

[shg]

Perhaps you could explain the genesis of the problem.

 

[Eric W]

It appears that the OP is looking for an application of the Binomial Theorem.

http://www.mathwords.com/b/binomial_probability_formula.htm

However, with the added twist that each question has a different probability of being correct, we can't use the same values of p and q for each question. I think we'd have to loop through the whole 2^13 (8192) permutations, calculate the probability for each permutation, then sum that into an array varying from 0 to 13, representing how many questions are correct. In fact, having done that, I get the results posted.

 

[bilbon]

Hi Gerald and SHG



What I'm after is being able to do this computation with VBA in Excel instead when it would save tremendously with time. Or maybe it goes with formulas.


I try to explain from beginning.
I get these percentages that are for 13 football matches.

The largest numbers in each match become the A-row the second largest becomes the B-row and the smallest becomes the C-row
Match % % %
1 14 25 61
2 92 6 2
3 49 30 21
4 50 27 23
5 69 20 11
6 48 30 22
7 48 30 22
8 49 28 23
9 56 25 19
10 28 29 43
11 64 22 14
12 67 19 14
13 19 30 51


and then I sort them like this to easily see the lines


% A % B % C
61 25 14
92 6 2
49 30 21
50 27 23
69 20 11
48 30 22
48 30 22
49 28 23
56 25 19
43 29 28
64 22 14
67 19 14
51 30 19


If we start from the A line with those percentages,
I would like to figure out how big the chance is to get 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 right on that line
and then the same on the B line and the C line .








According to a programme, there will be these chances as a percentage. For example, to get 7 right on the A line is 21.80% chance of getting 3 right on the B line is 25.54% chance


[TABLE="width: 244"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Number of Right[/TD]
[TD]Probability % A[/TD]
[TD]Probability % B[/TD]
[TD]Probability % C[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]0,06[/TD]
[TD]0,00[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]0,60[/TD]
[TD]0,00[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2,86[/TD]
[TD]0,00[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8,26[/TD]
[TD]0,01[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]16,01[/TD]
[TD]0,07[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]21,93[/TD]
[TD]0,39[/TD]
[TD]0,04[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]21,80[/TD]
[TD]1,66[/TD]
[TD]0,26[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]15,87[/TD]
[TD]5,24[/TD]
[TD]1,29[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8,42[/TD]
[TD]12,25[/TD]
[TD]4,70[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3,20[/TD]
[TD]20,95[/TD]
[TD]12,39[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0,84[/TD]
[TD]25,54[/TD]
[TD]23,12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0,14[/TD]
[TD]21,02[/TD]
[TD]28,93[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0,01[/TD]
[TD]10,48[/TD]
[TD]21,79[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0,00[/TD]
[TD]2,39[/TD]
[TD]7,47[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]This is the correct answer[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

It appears that the OP is looking for an application of the Binomial Theorem.

However, with the added twist that each question has a different probability of being correct, we can't use the same values of p and q for each question.

= Poisson Binomial, for which there are various methods to generate the probability mass function.

 

[shg]

Taking Stephen's suggestion,

[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][td="bgcolor:#C0C0C0"]

N​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Match​

[/td][td="bgcolor:#F3F3F3"]

%​

[/td][td="bgcolor:#F3F3F3"]

%​

[/td][td="bgcolor:#F3F3F3"]

%​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

1​

[/td][td="bgcolor:#F3F3F3"]

2​

[/td][td="bgcolor:#F3F3F3"]

3​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

k​

[/td][td="bgcolor:#F3F3F3"]

Pr(k)​

[/td][td="bgcolor:#F3F3F3"]

Pr(k)​

[/td][td="bgcolor:#F3F3F3"]

Pr(k)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

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]

0​

[/td][td="bgcolor:#CCFFFF"]

0.000 005​

[/td][td="bgcolor:#CCFFFF"]

0.023 946​

[/td][td="bgcolor:#CCFFFF"]

0.074 681​

[/td][td]K2:K15 and copied across: {=TRANSPOSE(PoissonBinom(F2:F14))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

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]

1​

[/td][td="bgcolor:#CCFFFF"]

0.000 134​

[/td][td="bgcolor:#CCFFFF"]

0.104 848​

[/td][td="bgcolor:#CCFFFF"]

0.217 900​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

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]

2​

[/td][td="bgcolor:#CCFFFF"]

0.001 417​

[/td][td="bgcolor:#CCFFFF"]

0.210 233​

[/td][td="bgcolor:#CCFFFF"]

0.289 332​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

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]

3​

[/td][td="bgcolor:#CCFFFF"]

0.008 399​

[/td][td="bgcolor:#CCFFFF"]

0.255 402​

[/td][td="bgcolor:#CCFFFF"]

0.231 244​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

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]

4​

[/td][td="bgcolor:#CCFFFF"]

0.032 029​

[/td][td="bgcolor:#CCFFFF"]

0.209 545​

[/td][td="bgcolor:#CCFFFF"]

0.123 944​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

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]

5​

[/td][td="bgcolor:#CCFFFF"]

0.084 242​

[/td][td="bgcolor:#CCFFFF"]

0.122 468​

[/td][td="bgcolor:#CCFFFF"]

0.046 963​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

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]

6​

[/td][td="bgcolor:#CCFFFF"]

0.158 675​

[/td][td="bgcolor:#CCFFFF"]

0.052 381​

[/td][td="bgcolor:#CCFFFF"]

0.012 912​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

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]

7​

[/td][td="bgcolor:#CCFFFF"]

0.217 988​

[/td][td="bgcolor:#CCFFFF"]

0.016 570​

[/td][td="bgcolor:#CCFFFF"]

0.002 600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

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]

8​

[/td][td="bgcolor:#CCFFFF"]

0.219 323​

[/td][td="bgcolor:#CCFFFF"]

0.003 867​

[/td][td="bgcolor:#CCFFFF"]

0.000 382​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

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]

9​

[/td][td="bgcolor:#CCFFFF"]

0.160 096​

[/td][td="bgcolor:#CCFFFF"]

0.000 655​

[/td][td="bgcolor:#CCFFFF"]

0.000 040​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

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]

10​

[/td][td="bgcolor:#CCFFFF"]

0.082 601​

[/td][td="bgcolor:#CCFFFF"]

0.000 078​

[/td][td="bgcolor:#CCFFFF"]

0.000 003​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

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]

11​

[/td][td="bgcolor:#CCFFFF"]

0.028 577​

[/td][td="bgcolor:#CCFFFF"]

0.000 006​

[/td][td="bgcolor:#CCFFFF"]

0.000 000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

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]

12​

[/td][td="bgcolor:#CCFFFF"]

0.005 951​

[/td][td="bgcolor:#CCFFFF"]

0.000 000​

[/td][td="bgcolor:#CCFFFF"]

0.000 000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

13​

[/td][td="bgcolor:#CCFFFF"]

0.000 564​

[/td][td="bgcolor:#CCFFFF"]

0.000 000​

[/td][td="bgcolor:#CCFFFF"]

0.000 000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/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 436​

[/td][td="bgcolor:#E5E5E5"]

1.000 000​

[/td][td="bgcolor:#E5E5E5"]

1.000 000​

[/td][td][/td][/tr]
[/table]

I expect the reason col K doesn't total closer to 1 is the numerical instability of the recursive formula used, as noted in the linked article.

The UDF:

Function PoissonBinom(rProb As Range) 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

  PoissonBinom = adPoissonBinom(p)
End Function

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

  ' https://en.wikipedia.org/wiki/Poisson_binomial_distribution#Probability_mass_function

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

  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 i = 1 To n
    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# / k) * (-1) ^ (i - 1) * Pr(k - i) * T(i)
    Next i
  Next k
  adPoissonBinom = Pr
End Function

 

[Eric W]

Given the layout from post #3 ,

	A	B	C	D	E	F	G	H	I
% A	% B	% C	Number of Right	Probability % A	Probability % B	Probability % C
16.01%
30%
16

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]61%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0.06%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.60%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2.86%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]27%[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8.26%[/TD]
[TD="align: right"]0.01%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]69%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]0.07%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]21.93%[/TD]
[TD="align: right"]0.39%[/TD]
[TD="align: right"]0.04%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]48%[/TD]

[TD="align: right"]22%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]21.80%[/TD]
[TD="align: right"]1.66%[/TD]
[TD="align: right"]0.26%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15.87%[/TD]
[TD="align: right"]5.24%[/TD]
[TD="align: right"]1.29%[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]56%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8.42%[/TD]
[TD="align: right"]12.25%[/TD]
[TD="align: right"]4.70%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]43%[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3.20%[/TD]
[TD="align: right"]20.95%[/TD]
[TD="align: right"]12.39%[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]64%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.84%[/TD]
[TD="align: right"]25.54%[/TD]
[TD="align: right"]23.12%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.14%[/TD]
[TD="align: right"]21.02%[/TD]
[TD="align: right"]28.93%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.01%[/TD]
[TD="align: right"]10.48%[/TD]
[TD="align: right"]21.79%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]2.39%[/TD]
[TD="align: right"]7.47%[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet12​

I used the non-recursive method, which, with only 8192 loops needed, did not take very long.

Sub test1()
Dim totpct(0 To 13, 1 To 3) As Double, i As Long, j As Long, NumOnes As Byte
Dim ix(1 To 13) As Byte

    pcts = Range("A2:C14").Value
    NumOnes = 0
    
NextTime:
    For j = 1 To 3
        wkpct = 1
        For i = 1 To 13
            If ix(i) = 0 Then
                wkpct = wkpct * (1 - pcts(i, j))
            Else
                wkpct = wkpct * pcts(i, j)
            End If
        Next i
    
        totpct(13 - NumOnes, j) = totpct(13 - NumOnes, j) + wkpct
    Next j
    
    For i = 1 To 13
        ix(i) = ix(i) + 1
        NumOnes = NumOnes + 1
        If ix(i) = 1 Then GoTo NextTime:
        NumOnes = NumOnes - 2
        ix(i) = 0
    Next i
    
    Range("F2:H15").Value = totpct
    
End Sub

The results I got match what's in post #3 , but they don't match up with shg's results. I believe that shg's algorithm reverses the order, Pr(k) should be Pr(13-k).

I'm a bit confused as to what the percentages represent though. Odds of beating a specific team? Why are you sorting the percentages left to right?