Data Matters with Excel®

Activity 1.3

The law of large numbers claims that as a random sample gets larger, the proportions in the sample tend to approach the probabilities that are generating those proportions. This project lets you check that for yourself.

Setting Up

In the first row, type in these column labels, one for each of the first five columns.

• Random Number
• Coin Flip: 1=Head, 0=Tail
• Number of Heads
• Number of Flips
• Percent Heads

In the second row, type in these equations, one for each column.

• For Random Number: =RAND()
• For Coin Flip: =ROUND(A2,0)
• For Number of Heads: =SUM(B$2:B2)
• For Number of Flips: =COUNT(B$2:B2)
• For Percent Heads: =C2/D2

Now set Decimal Places to zero. Select the column for Percent Heads by clicking on the E column label (the E at the top of the column). Select Format, then Cells. Click on the Number tab, then select Percentage. To set Decimal Places to zero, type the digit 0 in the decimal places box, then click OK.

Left-click and drag to select the cells in row 2 that contain the equations you entered. When you release, a black outline appears with a little black square on the bottom right. Click on that square and drag it down. This will fill the cells below with your equations.

Double-click on some of the cells that you just filled. You can see that the equations remain the same except that the row indicators change to reflect the new rows.

Notice that B$2 does not change at all. That’s what the $ does for you. It indicates that the 2 should not change when the equation is pasted into new rows.

What Do You Have?

Random Number is now filled with randomly selected values from between 0 and 1. That’s what RAND() gives you.

Coin Flip has the random numbers rounded so that there are no decimal places. That’s what ROUND(_,0) does. ROUND(_,1) would round to a single decimal place. The result is that if Random Number is lower than .5, Coin Flip is 0. If Random Number is .5 or higher, Coin Flip is 1. The chances that Random Number is lower than .5 are 50%, so this is like flipping a coin. Each 1 indicates a head. Each 0 indicates a tail.

Number of Heads has the number of heads that appear in Coin Flip (column B) from the second row to the current. It does this by summing all the values in column B from row 2 to whichever row you are looking at. For example, on row 7, Number of Heads has the sum of all the Coin Flips between row 2 and row 7. To get that sum, we use SUM(). On row 7, the equation is SUM(B$2:B7). B$2:B7 means “from cell B2 to cell B7.”

Number of Flips has the number of flips, which it finds with COUNT(B$2:B_). COUNT simply counts how many values are in the indicated range. For example, on row 7, COUNT(B$2:B7) is 6.

Percent Heads: is the percentage of flips that came up heads from row 2 to the current row. The percentages are calculated by dividing the number of heads by the number of flips.

Make a Graph

Select the column of percents by clicking on the E column label. Click on the diagram of a chart to get the chart wizard. (It is to the left of a question mark at the top of the worksheet.) Select Line, then Finish.

Look at the chart. It shows you the law of large numbers. The percentages range widely on the left, when the sample is still small. As the sample gets larger, the proportions tend to get closer and closer to 50%.

Look at Several Simulations

To replace the random numbers, press “F9” (at the top of your keyboard). Watch what happens each time you take a new sample.

Report

Print out some of the charts. Then write a few sentences explaining what this simulation shows you about where you expect proportions to fall when you flip coins.