Center Home -> Content Areas Home -> Math Home -> Project Activities -> Excel Activities ->

Simulating Random Events

Activity Description Activity Guide


In order for the random number generator to work properly in this activity, the Add In called Analysis ToolPak needs to be activated.  Pull down the Tools menu. If the Analysis ToolPak is activated, the menu will contain the item Data Analysis.... If not, to activate the Analysis ToolPak, under the Tools menu, choose Add-Ins... , check the Analysis ToolPak option and click OK.  The Data Analysis option should now appear under the Tools menu.  (Note:  On some versions of Excel, the Data Analysis option appears under the Data menu.)

This exploration uses a previously created Excel workbook called Coins and Dice. This workbook contains four worksheets labeled Coin Toss, One Die Toss, Two Die Toss and Empirical vs. Theoretical.   The labels for the worksheets appear on the gray tabs at the bottom of the screen.This file can be downloaded from the following website: Microsoft Excel Interactive Projects

Part 1: Simulating a Coin Toss

  • Before opening the Coins and Dice file, discuss the following questions:

    • A fair coin is flipped 10 times.  Have each student list a possible sequence of outcomes.  Discuss reasons for the sequences given.

    • A fair coin is flipped 10 times. The following is a list of possible sequences.
      HHTHTTHTHH
      TTTTTTTTTT
      HHHHHTTTTT
      HTHTHTHTHT
      HHHHHHHHHH

    • Discuss whether or not these sequences are equally likely to occur. 

    • What is the probability that heads occurs on any given flip?

    • If a coin is tossed 100 times, how many heads and tails do you predict will occur?  Justify your prediction.

  • Open the Coins and Dice workbook in Excel. Be sure the Coin Toss worksheet is open.  By pressing the F9 key, run an experiment of tossing a coin 100 times and observe the list of results, frequency table, and the graph.

    • Analyze the list of results.  Do heads and tails occur in any sort of pattern?

    • Compare the results of each student.  Does anyone’s results match their prediction? 

  • Run the experiment several times (use the F9 key).  Have students record the results of each experiment.  Discuss the outcomes of the experiments as a class. In particular:

    • What was the largest discrepancy between the number of heads and tails?

    • Were there any results (e.g., 50 heads and 50 tails, 43 heads and 57 tails) that appear to occur more often than others?  Discuss why some results might appear more frequently than others.

  • Suppose on one run of the experiment, a particular sequence of 30 heads and 70 tails results.  On the next run of the experiment, the results are a particular sequence of 50 heads and 50 tails.  Are the two sequences of results equally likely to occur?  Discuss.

  • In general, what is the probability of 30 heads and 70 tails occurring.  How does this compare to the probability of 50 heads and 50 tails?  Calculate both probabilities.

  • Recall and discuss the concept of independent events and the Law of Large of Numbers in the context of this coin toss problem.

Part 2: Simulating Rolling One Die

  • If a fair six-sided die is rolled, what are the possible outcomes? Is each possible outcome equally likely to occur?

  • If a fair die is rolled 100 times, predict the outcomes.  Justify your predictions.

  • Click on the One Die Toss tab at the bottom of the screen to open the next worksheet.  Run the experiment once and compare the results with your prediction.

    • Analyze the list of results.  Do the outcomes occur in any sort of pattern?

    • Compare the results of each student.  Does anyone’s results match their prediction? Discuss any similarities and or discrepancies between the results and your prediction.

  • Run the experiment several times (use the F9 key).  Have students record the results of each experiment.  Discuss the outcomes of the experiments as a class. Were there any possible outcomes that appear to occur more often than others?

  • Suppose on one run of the experiment, a particular sequence of results has 40 sixes.  On the next run of the experiment, the results are a particular sequence with 20 sixes.  Are the two sequences of results equally likely to occur?  Discuss.

  • In general, what is the probability of 40 sixes occurring out of 100 rolls?  How does this compare to the probability of 20 sixes occurring out of 100 rolls?  Calculate both probabilities.

  • What would you expect to happen if you rolled a die 1000 times? 10,000 times?  100,000 times?

Part 3: Simulating Rolling Two Dice

  • If two die are tossed, and the individual outcomes are summed together, what are the possible sums?  Are these sums equally likely to occur?

  • If two die are tossed 100 times, what do you predict for the distribution of sums?  Justify your prediction.

  • Click on the Two Die Toss tab at the bottom of the screen to open the next worksheet.  Run the experiment several times and compare the outcomes with your prediction.  Discuss any similarities and or discrepancies in the results.  Are there any sums that seem to be more likely than others to occur?

  • Have each student run the experiment 10 times and record which sum has the highest frequency after each trial.  Keep a frequency count of the class results on the board.  Over all experiments, are there any sums that seem to have the highest frequency more often than others? 

  • Discuss possible reasons why the sums of 2 and 12 do not occur as often as sums of 6, 7, and 8. Have the class generate the sample space for all possible outcomes when rolling two dice and the respective sums.

  • What is the theoretical probability of rolling each of the sums?  If two die are rolled 100 times, what is the theoretical distribution of sums? How does this distribution compare with the theoretical distribution for the coin toss and the one die toss? How does this distribution compare with your experimental results when rolling two dice? 

  • In order to compare the theoretical and empirical (experimental) distribution of sums for this experiment, you should click on the Empirical vs. Theoretical  tab to open the next worksheet. This worksheet will simulate the same experiment of rolling two dice 100 times.  However, to complete the analysis, you need to calculate the theoretical distribution in the gray boxes. 

  • Notice that the bar graph now displays both the theoretical and empirical frequencies.  Press F9 a few times and compare the experimental and theoretical frequencies.  How do they compare?

Part 4: Creating a Simulation of a Spinner

Now that you have analyzed three different random events, let’s explore how the simulations were created in Excel. 

  • Click on the One Die Toss tab to view the Die Toss worksheet.  Let’s further analyze the simulation:

    • How could you simulate tossing a die? Click on cell F4 and observe the function used. Interpret the function.

      1.  How could you set up the spreadsheet to repeat the die toss 100 times?  Scroll down the worksheet and observe where the “Results” column ends.

      2.  Once the spreadsheet randomly generates 100 die tosses, how are the results tallied? Click on cell B5 in the Frequency chart and interpret the function.  Click on the other cells in the frequency chart and observe how the function changes for each result being tallied.  Pay particular attention to the syntax used in the function.

      3.  How is the frequency data graphed?  Since the graph displays the frequencies, the range selected to make the graph was A4 to B10.

NOTE:  Pressing the F9 key in an Excel spreadsheet will automatically recalculate all formulas.

  • Now that you know the basic functions used to create the simulations, consider the following experiment:

    The following spinner is spun 100 times.  What is the frequency of spins that land in each of the four regions?

  • Open a new Excel workbook.  Create a simulation of the above experiment.  (Refer to the worksheets in the Coins and Dice workbook if necessary.)

Extensions:

  • Create a workbook containing three worksheets that simulates spinning the following spinner.

    •

(Note: The red and green areas are equal.  The blue area is 1/2 the size of the red area. The yellow area is four times as large as the blue area.)

  • On the first worksheet, simulate spinning the spinner 10 times.

  • On the second worksheet, simulate spinning the spinner 100 times.

  • On the third worksheet, simulate spinning the spinner 1000 times.

On each worksheet, be sure to include both a table and graph displaying the frequencies.  Use a textbox (found on the Draw toolbar) to include pertinent directions or questions for students.


Back to Project Activities | Back to Math Homepage

Send questions or comments here.
Last modified on July 27, 2001.