Dice and Probabilty: Day 2

Today we are going to use Microsoft Excel to analyze data and  the probability of rolling different numbers with two dice.  You can start this activity by opening the template that has been prepared for you.  You should notice that all the different combinations of rolls and their totals, which we found yesterday, are listed for you.  You should also see a column just to the left of them marked "Actual Probability."  This column represents the theoretical probability of each of those roll totals happening.  Note that this column is empty.  This is not a mistake!  It is your job to calculate these probabilities.  Instead of using a calculator, we will use the features of Excel to complete this task:

1. You will begin working on the "Theoretical" side of the spreadsheet, which is purple in color.  The first thing we will do is add up the total number of ways to roll 2 dice (or the total number of possible results).  We already know this number is 36, but let's use Excel to calculate it.  Click in the cell to the left of "total number of combos" and type "=sum("  Now click and hold in cell C4 and drag down to cell C14.  All of the combination totals should be highlighted at this point.  Now push enter.  The number "36" should appear in the appropriate cell.  This is one of many functions that Excel is capable of.

2. Next we want to calculate the theoretical probability of rolling different numbers.  We already know how to calculate these, but now we will use Excel to do so.  Start by clicking in the first cell under "theoretical probability."  Now you should type " = " then click on the cell to the left of the one you are working in, then " / " then click on the cell containing the total number of combinations that you just calculated.  Now you must put the cursor in front of the second cell you selected (which should be C16) and push the key F4, then Enter.  This will cause that number to stay the same as you calculate all of the rest of the probabilities down the column.  Instead of going through this process for every value, we can ask the program to do the same calculation all the way down the column.  This can be done by highlighting the cell you just calculated with all the ones below it that still need to be calculated (D4 through D14).  Then go to "Edit" then "Fill" then "Down."  You should now have a list of all of the probabilities in the "Theoretical Probability" column.  (Feel free to sum this column to ensure that it adds to 1.)

3. Now we will actually take some data of our own to prove this probability theory to ourselves.  Break into groups of 3 or 4.  Each group will have 2 dice to roll.  On a sheet of paper, make a chart that allows you to keep tallies of each of the totals rolled.  Try to finish at least 100 trials.  These should go very quickly if you work together and divide the work between your group.  After you have completed at least 100 trials, record the frequency of each number rolled in the "Frequency" column on the Experimental side of the spreadsheet.  The headings should be aqua-colored.

4.  We need to find the total number of trials to record at the bottom of the "Frequency" Column in cell (F16).  Sum the values like we did on the Theoretical side by typing in "=sum(" then highlighting all of the values and pressing the Enter key.  The total number of trials that you completed should appear in cell F16.

5.  Now we need to calculate the Experimental Probability just like we did for the Theoretical Probability.  Use the same technique.  You should notice that all of your numbers have many decimal places after them.  This can be rounded by highlighting all of those cells (G4 through G14), then "Format" then "Cells."  Then from the box that appears, chose the category "Number" then select the number of decimals as "2."  This will round all of your numbers so that they can be compared to our Theoretical Probabilities.

6.  In order to compare our Theoretical and Experimental Probabilities, we will represent them visually by using 2 different types of graphs.  Let's start with a bar graph.  First, hold down "control" then highlight the cell containing "Theoretical Probability" and the cells below it containing the probabilities and while still holding control, highlight "Experimental Probability" and the cells containing probabilities below it as well.  After both columns are highlighted, push the graph button at the top of the screen.  Choose "Column" for chart type, choose the graph in the upper left hand corner and select "Next."

7.  Your graph will not be accurate until you change the data labels for the x-axis to reflect the roll totals.  This can be done by switching from the "Data Range" tab to the "Series" tab at the top of the box.  In the "Series" box, find the "Category (x) axis labels" and type in "2,3,4,5,6,7,8,9,10,11,12" to represent the roll totals.  Then select "Next."

8.  Using the tabs at the top of the box, give your graph appropriate titles and labels for the different axes.  Then select "Next."  Choose to open your chart as an "object in sheet 1."  Now select "Finish."  Your graph should appear in your original spreadsheet.  Move it away from your data.  Sit back and look at your graph.  How can you analyze it?  What is interesting about it?  What does this graph tell you?

9.  Let's make one more graph that may make it easier for some people to analyze.  Follow the exact same process that we did for a bar graph, but instead select "Line" and choose the graph in the upper left hand corner.  You will see the same data represetned in a different way.  Are there any other graphs that you would like to try?  Now is your chance to explore.  Try different variables, and see what you can come up with.

After using Microsoft Excel to analyze your data, how close is your theoretical and experimental data?  How do you think we might be able to make these sets of data closer in value?

Most importantly, when playing a dice game with 2 dice, which number is most likely to be rolled?  Can you try to explain why?  Use your graphs to draw as many conclusions about this data as possible!

When you have finished your analysis, write a few paragraphs about what the graphs tell you and how you can interpret them.  Include an explanation of the variables and how they may or may not affect each other.  Also, include your strategy when playing a game like the one described in the introduction!