Peter has nine four-sided (pyramidal)
dice, each with faces numbered 1, 2, 3,
4.
Colin has six six-sided (cubic) dice,
each with faces numbered 1, 2, 3, 4, 5,
6.
Peter and Colin roll their dice and compare totals: the highest total wins. The result is a draw if the totals are equal.
What is the probability that Pyramidal Pete beats Cubic Colin? Give your answer rounded to seven decimal places in the form 0.abcdefg
This can be solved using just Excel -- well, Excel
and basic knowledge of probability theory.
Peter, with his 9 dice, can roll sums from 9 to 36. Colin, with
his 6 six-sided dice, can roll sums from 6 to 36. So, we can
create a 2D table listing when Peter will beat Colin.
Suppose we put Peter's possible results in N5:N32 and Colin's possible
results in Q2:AU2.
Then, in Q5 enter the formula =N($N5>Q$2). Copy Q5 to all the
cells in Q5:AU32. The cells will contain a 1 if Peter beats Colin
and a zero otherwise.
Next, we need to know the probability that Peter will roll each of the
totals 9 through 36 and that Colin will roll each of the totals 6
through 36.
The way to calculate each probability is to calculate the number of ways
in which Peter (or Colin) can roll each total divided by the total
number of possible rolls. The latter number, in the case of Peter
is 49, or 262144, and in the case of Colin 66, or
46656. For each player, we can get a complete list of all possible
dice results
using the approach documented at
Generate All Permutations. Copy Peter's list of permutations
from the workbook used to create Peter's list and paste the values at
C2:K262145. In L2 enter the formula =SUM(C2:K2) and copy it down
to L3:L262145. Similarly, copy and paste the values for Colin's
permutations intp a new sheet, named Colin, at C3:H46657. In I2
enter the formula =SUM(C2:H2) and copy it down to I2:I46657. Of
course, the values in each
of the enumerated results (the lists we just pasted) start at 0. So, in some empty cell enter 1,
copy this new cell, select the list of permutations and use
Paste Special... | Add operation.
This will modify the permutation list to start at 1.
Now, back on the 1st sheet, in O5 enter the formula
=COUNTIF($L$2:$L$262145,N5). This gives us the number of ways in
which Peter can roll that particular total. In P5 enter the
formula =O5/SUM($O$5:$O$32). This gives us the probability that
Peter will roll that total. Copy O5:P5 to rows 6:32. This
gives us the probability for each of Peter's totals. To do the
same for Colin, in Q3 enter the formula =COUNTIF(Colin!$I$2:$I$46657,Q2)
and in Q4 the formula =Q3/SUM($Q$3:$AU$3). Copy Q3:Q4 to columns
R:AU. Now, row 4 has the probability of each of Colin's totals.
Next, we modify the formula we had entered to calculate when Peter would
win to incorporate the probability of that particular win. Since
Peter and Colin's rolls are independent of each other, the probability
that Peter rolls the total N and Colin rolls the total M is given by
Probability(Peter rolls N) * Probability(Colin rolls M). So, modify the formula in Q5
to =N($N5>Q$2)*$P5*Q$4 and copy Q5 to Q5:AU32. In AV5 enter the
formula =SUM(Q5:AU5). This gives the probability that Peter wins
if he rolls a 9. Copy AV5 to AV6:AV32. This gives the
probabilities that Peter wins if he rolls the total given in column N.
Finally, in AV33 enter the formula =SUM(AV5:AV32) and format that cell
to show 7 decimal digits. This is the probability to 7 decimal
places that Peter beats Colin.
The Excel workbook
