# Thread: Calculate the probability of each number in 3 columns Thanks: 0 Likes: 0

1. ## Calculate the probability of each number in 3 columns

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.

 Match % % % Probability % A Probability % B Probability % C Number of Right Probability % A Probability % B Probability % C 1 14 25 61 61 25 14 13 0,06 0,00 0,00 2 92 6 2 92 6 2 12 0,60 0,00 0,00 3 49 30 21 49 30 21 11 2,86 0,00 0,00 4 50 27 23 50 27 23 10 8,26 0,01 0,00 5 69 20 11 69 20 11 9 16,01 0,07 0,00 6 48 30 22 48 30 22 8 21,93 0,39 0,04 7 48 30 22 48 30 22 7 21,80 1,66 0,26 8 49 28 23 49 28 23 6 15,87 5,24 1,29 9 56 25 19 56 25 19 5 8,42 12,25 4,70 10 28 29 43 43 29 28 4 3,20 20,95 12,39 11 64 22 14 64 22 14 3 0,84 25,54 23,12 12 67 19 14 67 19 14 2 0,14 21,02 28,93 13 19 30 51 51 30 19 1 0,01 10,48 21,79 0 0,00 2,39 7,47 100% 100% 100% This is the correct answer

2. ## Re: Calculate the probability of each number in 3 columns

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 ?

3. ## Re: Calculate the probability of each number in 3 columns

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.

 % A % B % C Number of Right Probability % A Probability % B Probability % C 61 25 14 13 0,06 0,00 0,00 92 6 2 12 0,60 0,00 0,00 49 30 21 11 2,86 0,00 0,00 50 27 23 10 8,26 0,01 0,00 69 20 11 9 16,01 0,07 0,00 48 30 22 8 21,93 0,39 0,04 48 30 22 7 21,80 1,66 0,26 49 28 23 6 15,87 5,24 1,29 56 25 19 5 8,42 12,25 4,70 43 29 28 4 3,20 20,95 12,39 64 22 14 3 0,84 25,54 23,12 67 19 14 2 0,14 21,02 28,93 51 30 19 1 0,01 10,48 21,79 0 0,00 2,39 7,47 100% 100% 100% This is the correct answer

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)

 % A 61% 92% 49% 50% 69% 48% 48% 49% 56% 43% 64% 67% 51% 13 0,06%

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.

 Error % % A % A % A % A % A % A % A % A % A % A % A % A % A 39% 39% 61% 61% 61% 61% 61% 61% 61% 61% 61% 61% 61% 61% 8% 92% 8% 92% 92% 92% 92% 92% 92% 92% 92% 92% 92% 92% 51% 49% 49% 51% 49% 49% 49% 49% 49% 49% 49% 49% 49% 49% 50% 50% 50% 50% 50% 50% 50% 50% 50% 50% 50% 50% 50% 50% 31% 69% 69% 69% 69% 31% 69% 69% 69% 69% 69% 69% 69% 69% 52% 48% 48% 48% 48% 48% 52% 48% 48% 48% 48% 48% 48% 48% 52% 48% 48% 48% 48% 48% 48% 52% 48% 48% 48% 48% 48% 48% 51% 49% 49% 49% 49% 49% 49% 49% 51% 49% 49% 49% 49% 49% 44% 56% 56% 56% 56% 56% 56% 56% 56% 44% 56% 56% 56% 56% 57% 43% 43% 43% 43% 43% 43% 43% 43% 43% 57% 43% 43% 43% 36% 64% 64% 64% 64% 64% 64% 64% 64% 64% 64% 36% 64% 64% 33% 67% 67% 67% 67% 67% 67% 67% 67% 67% 67% 67% 33% 67% 49% 51% 51% 51% 51% 51% 51% 51% 51% 51% 51% 51% 51% 49% 0,04% 0,00% 0,06% 0,06% 0,03% 0,06% 0,06% 0,06% 0,04% 0,07% 0,03% 0,03% 0,05% 12 0,60%

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

4. ## Re: Calculate the probability of each number in 3 columns

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.

5. ## Re: Calculate the probability of each number in 3 columns

Perhaps you could explain the genesis of the problem.

6. ## Re: Calculate the probability of each number in 3 columns

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

http://www.mathwords.com/b/binomial_...ty_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.

7. ## Re: Calculate the probability of each number in 3 columns

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

 Number of Right Probability % A Probability % B Probability % C 13 0,06 0,00 0,00 12 0,60 0,00 0,00 11 2,86 0,00 0,00 10 8,26 0,01 0,00 9 16,01 0,07 0,00 8 21,93 0,39 0,04 7 21,80 1,66 0,26 6 15,87 5,24 1,29 5 8,42 12,25 4,70 4 3,20 20,95 12,39 3 0,84 25,54 23,12 2 0,14 21,02 28,93 1 0,01 10,48 21,79 0 0,00 2,39 7,47 100% 100% 100% This is the correct answer

8. ## Re: Calculate the probability of each number in 3 columns

Originally Posted by Eric W
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.

9. ## Re: Calculate the probability of each number in 3 columns

Taking Stephen's suggestion,

 A B C D E F G H I J K L M N 1 Match % % % 1 2 3 k Pr(k) Pr(k) Pr(k) 2 1 14% 25% 61% 61% 25% 14% 0 0.000 005 0.023 946 0.074 681 K2:K15 and copied across: {=TRANSPOSE(PoissonBinom(F2:F14))} 3 2 92% 6% 2% 92% 6% 2% 1 0.000 134 0.104 848 0.217 900 4 3 49% 30% 21% 49% 30% 21% 2 0.001 417 0.210 233 0.289 332 5 4 50% 27% 23% 50% 27% 23% 3 0.008 399 0.255 402 0.231 244 6 5 69% 20% 11% 69% 20% 11% 4 0.032 029 0.209 545 0.123 944 7 6 48% 30% 22% 48% 30% 22% 5 0.084 242 0.122 468 0.046 963 8 7 48% 30% 22% 48% 30% 22% 6 0.158 675 0.052 381 0.012 912 9 8 49% 28% 23% 49% 28% 23% 7 0.217 988 0.016 570 0.002 600 10 9 56% 25% 19% 56% 25% 19% 8 0.219 323 0.003 867 0.000 382 11 10 28% 29% 43% 43% 29% 28% 9 0.160 096 0.000 655 0.000 040 12 11 64% 22% 14% 64% 22% 14% 10 0.082 601 0.000 078 0.000 003 13 12 67% 19% 14% 67% 19% 14% 11 0.028 577 0.000 006 0.000 000 14 13 19% 30% 51% 51% 30% 19% 12 0.005 951 0.000 000 0.000 000 15 13 0.000 564 0.000 000 0.000 000 16 Total 0.999 436 1.000 000 1.000 000

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:

Code:
```Function PoissonBinom(rProb As Range) As Variant
' shg 2018

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

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
End Function```

10. ## Re: Calculate the probability of each number in 3 columns

Given the layout from post #3 ,

A B C D E F G H I
1 % A % B % C Number of Right Probability % A Probability % B Probability % C
2 61% 25% 14% 13 0.06% 0.00% 0.00%
3 92% 6% 2% 12 0.60% 0.00% 0.00%
4 49% 30% 21% 11 2.86% 0.00% 0.00%
5 50% 27% 23% 10 8.26% 0.01% 0.00%
6 69% 20% 11% 9 16.01% 0.07% 0.00%
7 48% 30% 22% 8 21.93% 0.39% 0.04%
8 48% 30% 22% 7 21.80% 1.66% 0.26%
9 49% 28% 23% 6 15.87% 5.24% 1.29%
10 56% 25% 19% 5 8.42% 12.25% 4.70%
11 43% 29% 28% 4 3.20% 20.95% 12.39%
12 64% 22% 14% 3 0.84% 25.54% 23.12%
13 67% 19% 14% 2 0.14% 21.02% 28.93%
14 51% 30% 19% 1 0.01% 10.48% 21.79%
15 0 0.00% 2.39% 7.47%
16 100.00% 100.00% 100.00%
Sheet12

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

Code:
```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?