Lab 2 Simulation-2

How To Simulate Rolling a Pair of Dice and Recording the Sum Using Excel

  • Open Microsoft Excel 2016
  • Input the numbers 1, 2, 3, 4, 5, and 6 in the first six cells of column 1.
  • Input the numbers 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, and 12 in column 2.
  • Simulate the roll of the first die: In the Data menu, click on Data Analysis.  If your copy of Excel does not have the data analysis package displayed, use the posted instructions to activate the package.  Data Analysis is already installed in the Excel 2016, you just have to turn it on.
    • Scroll down and select Sampling.  Click OK.
    • Fill out the window using the following values then click OK:

      Input Range: A1:A6, Random Number of Samples: 200, and Output Range: D1

    • Column D will then be populated with simulated roll of the first die.
  • Repeat step 4 above, however, Change the Output Range to E1.
  • Click in cell F1 and type the following: “=SUM(D1:E1)” then press ENTER.
  • Click in cell F1 again and select Home -> COPY.
  • Put the mouse arrow over cell F2 and drag down so that column F is highlighted all the way down to row 200.
  • Once all 200 cells are selected in column F, select Home -> PASTE.
  • Column F is now populated with the simulated sum of the 200 rolls of the two dice.

How to count up (and summarize in a table) the sums generated by the simulation Using Excel:

  • In the Data menu, click on Data Analysis
    • Scroll down and select Histogram.  Click OK.
    • Fill out the window with the following values and boxes checked and click OK:Input Range: F1:F200, Bin Range: B1:B11, and Output Range: H1
    • You will then see a table summarizing the frequency of each possible sum, as pictured below (everyone’s values will be different).  You should ignore the row in the table labeled “More”. (That row should be 0.  If it isn’t, something went wrong.)
    • What the Bin Summary Table should look like
    • IMPORTANT:  convert these frequencies to relative frequencies by dividing each frequency by the total number of simulated rolls.
    • Create a table in your Word document of the relative frequencies by filling in the blanks of a table like the one pictured below before continuing! Use these values to compare to the true probabilities.
      200 rolls x 2 3 4 5 6 7 8 9 10 11 12
      P(x)

    Repeat steps A and B above, however, increase the number of simulated rolls from 200 to 20000.

    You will need to make the following changes to the process above:

    Adjusted Nunber of Samples, Change Number of samples to 20000 Adjusted Bin summary table, Adjust the Input Range to F1:F20000

    And in Step A8, make sure you drag the sum command down to all 20000 dice rolls.