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)

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)

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.

Dunno anything about PERT distributions, other than that they're like a smooth version of a triangular distribution, and there are three- and four-parameter versions.

Regardless, the sum of 100 of them (of random variables of any distribution) converges to a normal distribution.

30 (I think), because it uses a ParamArray.

EDIT: After a short test, I retract that; I don't know what the upper bound is. Maybe there is none.