Data Matters with Excel®

Activity 7.3

Section 7.3 suggests two ways to estimate the population variance from data in groups. One way looks at how the group means vary. The other way looks at how the observations within each group vary from the group mean. In this project, you will collect these estimates from many random samples from Rep US Sample. You can then explore the estimates and see whether they seem to work well.

To do this, you will use a strategy you have used before. You will set Excel to calculate the two variances on the Stats worksheet of the workbook you used in the last project. Then you will use a macro to take samples and record the variance estimates.

Open the workbook that you used last in the project in Section 6.3. Empty the Stats worksheet by right-clicking the upper-left gray cell and selecting Clear Contents.

Fill in the Stats worksheet as shown below. The boldfaced entries have to be typed by hand. The rest you can get by dragging the formula. Until you fill in the observations in column B, Excel will mark the cells containing equations with “#DIV/0!” to let you know that the formula for the mean is dividing by 0. That’s okay. It will get straightened out when you put the observations in.

The macro will fill in the observations. You can change the number of observations in a group by adding or subtracting rows and dragging the formulas in C, D, and E as needed. If you add groups, you will have to set the formula for the new groups in column C to get the mean of each new group.

Review the plan above. Make sure you understand what the sheet does.

Choose which column of data you want to work with. The macro below is set up to take data from the fifth column (Interest Income).

The macro will work only with data that does not have missing values. So it won’t work with education or year of entry.

Here is the macro that will collect the values in column I (the ninth column).

Sub Sampling()
  Randomize Timer
  For r = 2 To 55           ‘Set your number of samples by changing 55.
    For SampleRow = 2 To 12 ‘Set to your total sample size by changing 12.
      dataRow = 2 + Fix(50000 * Rnd)
      Worksheets("Data").Rows(dataRow).Copy
      Worksheets("Sample").Rows(SampleRow).PasteSpecial
    Next SampleRow
    Worksheets("Sample").Columns(5).Copy ‘Change “5” to copy your column.
    Worksheets("Stats").Columns(2).PasteSpecial
    Worksheets("Stats").Cells(r, 9).Value = Cells(2, 8).Value
  Next r
End Sub

Get the mean and a histogram of the estimated variances. In the Data worksheet, you can get the population variance with =var() .

So far you have estimated the variance from the variation within each group. How did you do?

To get the variance calculated from the variation between groups, insert a column by right-clicking on D and selecting Insert. Then edit the worksheet to fit the following scheme. Note the changes you have to make to column E.

Because the variance estimate is now one column over, we need to make some changes to the macro. Here’s the macro with the two changes noted in boldface on the third-to-last line.

Sub Sampling()
  Randomize Timer
  For r = 2 To 55 ‘Set your number of samples by changing 55.
    For SampleRow = 2 To 12 ‘Set to your total sample size by changing 12.
      dataRow = 2 + Fix(50000 * Rnd)
      Worksheets("Data").Rows(dataRow).Copy
      Worksheets("Sample").Rows(SampleRow).PasteSpecial
    Next SampleRow
    Worksheets("Sample").Columns(5).Copy ‘Change “5” to copy your column.
    Worksheets("Stats").Columns(2).PasteSpecial
    Worksheets("Stats").Cells(r, 10).Value = Cells(2, 9).Value
  Next r
End Sub

How do the estimated variances compare with the real variance of the population? Do the claims in Section 7.3 seem sensible?

Try different sample sizes. Would you like to propose any warnings for these variance estimates?