Data Matters with Excel®

Activity 2.3

Section 2.3 makes some strong claims about the specifics of the bell curves that samples’ proportions fall into. It says that you can calculate something called the standard error with this equation:

This section also says that if you collect a lot of random samples and sort them from the smallest proportion to the largest, you will see this pattern.

• About 2.5% will be more than two standard errors below the probability.
• About 16% will be more than one standard error below the probability.
• About 50% will be below and 50% will be above the probability.
• About 16% will be more than one standard error above the probability, so about 67% will be within one standard error of the probability.
• About 2.5% will be more than two standard errors above the probability, so about 95% will be within two standard errors of the probability and about 5% will be more than two standard errors away from the probability.

That’s a lot of claims. In this project you will check these claims to see where they hold up and where they don’t. You are going to collect the same sorts of proportions you collected in Section 2.2. You will then sort them and work your way up, checking each of the claims.

Pick your probability and sample size. Before you proceed, calculate the standard error and find what you get when you add and subtract one and two standard errors from your probability.

Follow the same steps as in Section 2.2. Set the probability by changing the equation for column B. For example, if you want to use a probability of .67, type in =A2<.67 .

Start with 1,000 samples. The way to do that is edit the macro so it looks like this (with the changed line in boldface).

Sub Sampling()
  For recordingRow = 2 To 1001
    Calculate
    Cells(recordingRow, 6).Value = Cells(2, 5).Value
  Next recordingRow
End Sub

(Notice there is no comma in “1001.” A comma would confuse Excel.)

Once you have your column of sample proportions, sort them by selecting the proportions column (click on the F in gray at the top) and clicking on the Sort Ascending button at the top of the screen. The Sort Ascending button has an “A” over a “Z” and a down-arrow on it.

Now you can scroll down and check the claims. Remember that row 1 has the name, so row 251 has the proportion that is 250th from the bottom.

Try other sample sizes and other probabilities. What sample sizes and/or proportions cause trouble for the claims about where the samples’ proportions fall? What troubles do they cause? Try other numbers of samples, such as 10. Does that cause trouble?