Data Matters with Excel®

Activity 4.1

Section 4.1 includes this table from Data Matters. It makes claims about the p-values of some specific z-values.

Don’t take my word for it. In this project, you will check the table for yourself. Here are the steps.

1. Pick a population proportion and sample size. (In these instructions, I will show you how to do this with a population proportion of 50% and a sample size of 2,500. You can adjust the instructions to work for the population proportion and sample size you want.)
2. Using your software, create 100 samples from that population and save the proportions.
3. Transform the proportions into z-values.
4. Sort the resulting z-values from lowest to highest and check the entries in Table 4.1.2 against what happened in your simulation.

Step 1: Pick a population proportion and sample size.

In these instructions, I am working with a population proportion of 50% and a sample size of 2,500. Wherever the instructions say “2,500,” put in your sample size. Wherever the instructions say “.5,” put in your population proportion.

Step 2: Using your software, create 100 samples from that population and save the proportions.

To label the first column, type Random into the top cell of column A. Type =RAND() into the second row of column A.

Label the second column Draw and type =A2<.5 into the second row.

The population proportion I’m using is .5. If you are using a population proportion of .73, then the formula for B2 is =A2<.73 . Use whatever population proportion you are working with.

Select the second row of the first two columns, click on the small black square, and drag down to row 2,501. That gives you a sample size of 2,500. If you want to work with another sample size, drag to a different row.

Label the third column Proportion and type =countif(B:B,TRUE)/2500 into the second row.

Label the fourth column Proportions and use this version of last section’s macro to take 100 samples (the changes are in bold).

Sub Record ()
  For sample = 2 TO 101
    Calculate
    Cells(sample, 4).Value = Cells(2,3).Value
  Next sample
End Sub

Now the macro is taking 100 samples and recording from column 3 into column 4.

Step 3: Transform the proportions into z-values.

Now we need the standard error of proportions. The standard error depends on the sample size and the population proportion of the null hypothesis. These instructions are for 2,500 observations from a population with a proportion at .5.

Label the fifth column Z and enter this formula into the second row: =(D2-.5)/.01 . That formula is the formula for a z-value:

(the sample proportion – the population proportion)/the standard error

Put in the population proportion and standard error you are working with. Select the formula, click on the little black box, and drag it down to row 101.

Step 4: Sort the resulting z-values from lowest to highest and check the entries in Table 4.1.2 against what happened in your simulation.

Select the column of the z-values by clicking on the E in gray and clicking on the Sort Ascending button at the top of the screen.

How do the claims in Table 4.1.2 look? Believable? Like hooey?

For greater precision, increase the number of samples.

On the other hand, try a sample size of 4. How do the p-values look now?