You are on the Home/Other Tutorials/Project Euler/Problem 205 page
Google
Web This Site

Project Euler - Problem 205

More about Project Euler

Problem description

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

Solution

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