You roll a pair of dice. My task is to predict which numbers will roll (total from both dice). How would I go about it?
It’s obviously a probability problem. I want to find the probability of each roll for all possible outcomes. How would I arrange and solve it in Excel?
Answer: Quite easily and with lots of joy 🙂
First, I would set up a matrix that’ll cover all the outcomes per roll. We’re rolling 2 dice, so for each die, the possible outcomes are 1, 2,….6.
With a quick formula I create the following matrix. It could be done by hand since it’s only 36 values, but why?
Ok, then the next step is to calculate the chance of each outcome/roll. The values then would be 2 through 12. So, I need to find the chance of 2 (die#1:1, die#2:1) overall. Meaning, how many times 2 appears in the matrix above.
Again using a COUNTIF(matrix1, roll), I can find that easily where matrix1 is a named range for the matrix cells and 2 is the value of roll in this case. Then I do the same for rest of the rolls including for 12.
I can layout those numbers next to each roll (darker blue column) in a table such as below. And for each row, it’s now a cinch to calculate the probabilities.
So, for example, 2 appeared 1 time in the matrix (you can verify it yourself above) out of 36 cells in the matrix yields 2.8% probability. And so on until the maximum possible value of 12.
To put icing on the cake, here’s a visual combining both chance and probability for all outcomes:
Well, that’s what the statistics said. If you roll the pair, what do you get? How many times did you roll? Do the outcome start to look like above after a good number of rolls? How many rolls did it take?
