ISP 121 Activity 2
Fundamental Concepts of Probability and Simulation of Experiments using Excel
1. One of the distinctions we made in class is the difference between theoretical, relative frequency, and "personal" interpretations of probability. In this activity we will explore the difference between theoretical probability and relative frequency through simulation using Excel.
a) Before we start, discuss with your partner whether the following statement is true or false:
"If you flip an unbiased coin many, many times, the fraction of the the flips that are heads and the fraction of the flips that are tails will be close to equal."
Do you think the following statement is true or false?
"If you flip an unbiased coin many, many times, the number of heads and tails will be close to equal."
In your Word document, write down your answers and explain your reasoning.
b) Let's simulate flipping an unbiased coin in Excel. To do so, we will use the Random Number Generation feature of the Data Analysis Tools.
Open a blank Excel file. Somewhere other than A1, type the following table into Excel:
| 1 | 0.5 |
| 0 | 0.5 |
In this table, we are setting up theoretical probabilities. "1" will represent heads, and "0" will represent tails. The theoretical probability for each is 0.5.
Next click on Tools->Data Analysis ->Random Number Generation. (Note if your version of Excel does not have the Data Analysis tools listed here, click on Tools->Add ins and then click the Analysis ToolPak. The Data Analysis tools will now appear in the Tools dropdown.)
For starters, we'll have 1 variable and 10 random numbers. Choose a "discrete" distribution, and highlight the table you typed in as the "Value and Probability Input Range." Finally choose the output range to be cell A1. Here is what it will look like:
Click OK, and if all is well, you will have 10 random 0's and 1's in a column starting in cell A1.
Paste into your Word document the 10 random flips, and in your Word document, answer the questions: did you get 5 heads and 5 tails? How many of each did you get?
c) Repeat part b) with 100 random flips. Trying to count the 1's and 0's with 100 flips is tedious. While you could sort the column and use the count feature on the bottom of the Excel screen, there is a function in Excel that can quickly count how many 1's and 0's you got. Click on an empty cell. Under the function (fx) icon, choose the Statistical category and then the COUNTIF function. For Range, highlight the column of randomly generated numbers, and for Criteria, type in either 1 or 0. Click OK. The number that appears in the cell is the number of 1's or 0's in the list of numbers. As in part b) record how many heads and tails you got. Did you get the same number of each? How many more heads than tails (or vice versa) did you get?
d) Finally repeat the experiment for 1000, 10000, and 30000 flips.
Fill in the following table using your data from parts b, c, and d. (Paste the table in your Excel workbook, fill it in, and finally paste it into Word document.) Remember the "relative frequency" of an event is
| Trials | Number of Heads (1's) | Number of Tails (0's) | Difference in number of heads and tails | Relative Frequency of Heads | Relative Frequency of Tails |
Ratio of Head to Tails |
| 10 | ||||||
| 100 | ||||||
| 1000 | ||||||
| 10000 | ||||||
| 30000 |
e) In a well-written paragraph, describe in your own words the patterns you see in the table. Does anything surprise you in the table?
f) Go back to the statements in part a). Based on the what you found above, were your answers correct? Explain.
2. With your group, figure out how to simulate rolling of a single die in Excel. (A die is a cube whose six sides are numbered 1 through 6 and designed so that each side is equally likely to face upwards when the cube is thrown.)
a. After you figure out how to simulate rolling a single die, simulate 300 rolls, and fill in the following table. Paste table in your Word document.
| Die Outcome | Observed Frequency | Theoretical Frequency | Deviation from Theoretical | Relative Frequency |
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 |
b. Next simulate 30000 rolls, and fill in the following table. Paste table in your Word document.
| Die Outcome | Observed Frequency | Theoretical Frequency | Deviation from Theoretical | Relative Frequency |
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 |
c. In a well-written paragraph, describe the patterns you see in the two data tables. What is similar between these results and the table of coin flips in question 1?
d. Critique the statement "If I roll a die many, many times, the number of 1's, 2's, 3's, 4's, 5's, and 6's that appear are about equal." Is it true? What is true? (Hint. Your table shows what is true.)