+ Reply to Thread
Results 1 to 7 of 7

Combining Probability Distributions in Excel

  1. #1
    Registered User
    Join Date
    08-19-2019
    Location
    El Segundo
    MS-Off Ver
    365
    Posts
    10

    Combining Probability Distributions in Excel

    Greetings! I'm struggling with a problem of combining Probability Distribution Functions in excel. I've set up a "simple" example to show what I'm looking for. In this example, we have PDFs for a two standard 6-sided dice, a 10-sided die, and a 6-sided die with numbers ranging from 10-15 on each side. I want to come up with a new PDF for rolling all the dice together. The PDF for each d6 die would be 0.167 for each of the numbers 1 to 6 (cells F6:K7). The PDF for the d10 would be 0.1 for each number from 1 to 10 (cells F8:O8). And the d6* would be 0.167 for each of the numbers 10 to 15 (O9:O15). I know how to do the combinations by hand and I've done various combinations in rows 11, 12, and 13. I'm looking for an approach/technique/formula for calculating the new PDF for all the dice combined using the information provided (e.g. referencing cells F5:AA9). Also know that this is a simplified version of the project I am working which involves PERT distributions (not normal or uniform).

    Any help you can provide would be appreciated! Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,539

    Re: Combining Probability Distributions in Excel

    You can do this by multiplying polynomials; the resulting exponents are the sum, and the coefficients are the number of ways to get there.

    A normal six-sided die is the poly x + x^2 + x^3 + x^4 + x^5 + x^6

    The 10-sided die is x + x^2 + x^3 + ... + x^10

    The odd die is x^10 + x^11 + x^12 + x^13 + x^14 + x^15

    A
    B
    C
    D
    E
    F
    G
    1
    Sum
    Die1
    Die2
    Die3
    Die4
    Ways
    2
    0
    0
    0
    0
    0
    0
    F2:F38: {=PolyProd(B2:B8, C2:C8, D2:D12, E2:E17)}
    3
    1
    1
    1
    1
    0
    0
    4
    2
    1
    1
    1
    0
    0
    5
    3
    1
    1
    1
    0
    0
    6
    4
    1
    1
    1
    0
    0
    7
    5
    1
    1
    1
    0
    0
    8
    6
    1
    1
    1
    0
    0
    9
    7
    1
    0
    0
    10
    8
    1
    0
    0
    11
    9
    1
    0
    0
    12
    10
    1
    1
    0
    13
    11
    1
    0
    14
    12
    1
    0
    15
    13
    1
    1
    16
    14
    1
    4
    17
    15
    1
    10
    18
    16
    20
    19
    17
    35
    20
    18
    56
    21
    19
    81
    22
    20
    108
    23
    21
    135
    24
    22
    160
    25
    23
    180
    26
    24
    192
    27
    25
    196
    28
    26
    192
    29
    27
    180
    30
    28
    160
    31
    29
    135
    32
    30
    108
    33
    31
    81
    34
    32
    56
    35
    33
    35
    36
    34
    20
    37
    35
    10
    38
    36
    4
    39
    37
    1


    PolyProd() is a user-defined function.

    The result above is the same thing you'd get if you multiplied out by hand

    (x + x^2 + x^3 + x^4 + x^5 + x^6) * (x + x^2 + x^3 + x^4 + x^5 + x^6) * (x + x^2 + x^3 + x^4 + x^5 + x^6 + x^7 + x^8 + x^9 + x^10) * (x^10 + x^11 + x^12 + x^13 + x^14 + x^15)
    Attached Files Attached Files
    Last edited by shg; 12-04-2019 at 09:34 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-19-2019
    Location
    El Segundo
    MS-Off Ver
    365
    Posts
    10

    Re: Combining Probability Distributions in Excel

    Sorry, I'm not seeing it. I know you said the PolyProd is a user defined function but what does it contain specifically? Can you show me the full formula in F2, F3, F4, etc? Thank you!

  4. #4
    Registered User
    Join Date
    08-19-2019
    Location
    El Segundo
    MS-Off Ver
    365
    Posts
    10

    Re: Combining Probability Distributions in Excel

    Also, the values will be all less than 1 as this is a normalized distribution and I don't know how many sides there will be. Also, the values could be different from each other. For example, the probability of a 6 might be higher than a 1 for the 6-sided dice in some cases.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,539

    Re: Combining Probability Distributions in Excel

    I know you said the PolyProd is a user defined function but what does it contain specifically?
    The exact formula is as shown, a single formula array-entered into F2:F39. The function PolyProd multiplies polynomials just as you were taught to do by hand in algebra class. The code is in the workbook.

    Also, the values will be all less than 1 as this is a normalized distribution
    To calculate the probability mass function, divide all the numbers by their sum, which is 6 x 6 x 10 x 6 = 2160, or normalize the poly coefficients (see below).

    Also, the values could be different from each other. For example, the probability of a 6 might be higher than a 1 for the 6-sided dice in some cases.
    Here's the PMF for a pair of dice where one favors 1's and the other favors 6's:

    B
    C
    D
    E
    2
    Sum
    Die1
    Die2
    PMF
    3
    0
    0
    0
    0.000
    4
    1
    0.5
    0.1
    0.000
    5
    2
    0.1
    0.1
    0.050
    6
    3
    0.1
    0.1
    0.060
    7
    4
    0.1
    0.1
    0.070
    8
    5
    0.1
    0.1
    0.080
    9
    6
    0.1
    0.5
    0.090
    10
    7
    0.300
    11
    8
    0.090
    12
    9
    0.080
    13
    10
    0.070
    14
    11
    0.060
    15
    12
    0.050


    The array formula in E3:E15 is =PolyProd(C3:C9, D3:D9)
    Last edited by shg; Yesterday at 01:10 PM.

  6. #6
    Registered User
    Join Date
    08-19-2019
    Location
    El Segundo
    MS-Off Ver
    365
    Posts
    10

    Re: Combining Probability Distributions in Excel

    Ok, thanks. Its working (sort of). Unfortunately, this doesn't scale elegantly. Or at least I can't scale it. For example, if you do the same thing for 100 dice that are each 100-sided which is what I am trying to do in my RL project, the spreadsheet just hangs. Suggestions? Any way to make this process capable of large numbers of runs?

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,539

    Re: Combining Probability Distributions in Excel

    Unfortunately, this doesn't scale elegantly.
    In fairness, I answered the question you asked.

    if you do the same thing for 100 dice that are each 100-sided which is what I am trying to do in my RL project, the spreadsheet just hangs.
    With 100 dice, I wouldn't bother. If the sides are numbered 1 to 100, then the mean role is 50.5, i.e., (100 + 1)/2.

    The variance of the rolls of a single 100-sided die is {=AVERAGE((ROW(INDIRECT("1:100")) - 50.5)^2)} = 833.25

    The sum of independent random variables converges to a normal distribution by the Central Limit Theorem.

    If you roll 100 100-sided dice together, then the mean roll is 5050, and the variance is 100 times the variance of a single roll -- so 83,325. The SD is the square root of that, or about 288.7. So if you generate random normal variates with mean 5500 and SD 288.7 you'll get a good simulation of your 100D100 dice rolls: =ROUND(NORM.INV(RAND(), 5050, 288.7), 0)

    If the face probabilities are all the same (i.e., 0.01), there's another function (PolyExp) that will raise that to the 100th power in a couple of seconds. See Sheet3.
    Attached Files Attached Files
    Last edited by shg; Yesterday at 11:14 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1