# Combining Probability Distributions in Excel

1. ## 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).  Register To Reply

2. ## 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)  Register To Reply

3. ## 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!  Register To Reply

4. ## 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.  Register To Reply

5. ## 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)  Register To Reply

6. ## 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?  Register To Reply

7. ## Re: Combining Probability Distributions in Excel

Unfortunately, this doesn't scale elegantly.

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.  Register To Reply