Statistical Analysis
Two-Dice Sum Probability Distribution, Tail Thresholds & Expected Payoff Formulas
Calculate exact 2d6 discrete convolution ways 6 - ABS(Sum - 7), closed-form tail probabilities P(Sum >= Target), and expected game value without volatile RANDARRAY recalculation.
Why exact combinatorial formulas beat volatile Monte Carlo dice sheets
Simulating dice rolls with RANDBETWEEN(1, 6) + RANDBETWEEN(1, 6) changes on every workbook edit and requires tens of thousands of rows to converge to 3 decimal places. For two fair 6-sided dice (36 equiprobable ordered pairs), the exact count of pairs summing to s in 2..12 follows the symmetric triangle MAX(0, 6 - ABS(s - 7)).
Summing the upper triangle yields a single closed-form expression for rolling at least T: IF(T >= 7, (13 - T) * (14 - T) / 2, 36 - (T - 1) * (T - 2) / 2) ways out of 36, while SUMPRODUCT(Ways, Net_Payout) / 36 computes exact expected value.
| Target Sum T | Exact Ways (= T) | Exact Ways (>= T) | P(Sum >= T) |
|---|---|---|---|
| T = 2 | 1 / 36 | 36 / 36 | 1.0000 |
| T = 7 (Mode) | 6 / 36 | 21 / 36 | 0.5833 |
| T = 10 | 3 / 36 | 6 / 36 | 0.1667 |
| T = 12 | 1 / 36 | 1 / 36 | 0.0278 |
Exact 2d6 Point & Upper-Tail Probability Formulas
Compute exact ordered-pair counts out of 36 for both = T and >= T.
=ROUND(IF(B2 >= 7, (13 - B2) * (14 - B2) / 2, 36 - (B2 - 1) * (B2 - 2) / 2) / 36, 4)Expected Net Payoff Across Outcome Buckets (SUMPRODUCT)
Weight each mutually exclusive outcome bucket's net payout by its exact count over 36.
=ROUND(SUMPRODUCT(E2:E4, F2:F4) / 36, 4)Try the example
Use the original values or change them to test your understanding.