Calculate the probability of each number in 3 columns
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

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

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Calculate the probability of each number in 3 columns

    Perhaps you could explain the genesis of the problem.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    Board Regular
    Join Date
    Dec 2011
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,457
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the probability of each number in 3 columns

    Quote Originally Posted by Eric W View Post
    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. #9
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default 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
      ' 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

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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?
    Last edited by Eric W; Dec 19th, 2018 at 03:55 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •