Data Matters with Excel®

Activity 5.2

There are two tasks in this project.

1. Take many random samples from the representative U.S. sample and save the samples’ means, then get a histogram of the samples’ means.
2. Collect a distribution of proportions that is normally distributed and check the symmetry.

Here’s how to do them.

1. Take many random samples from the representative U.S. sample and save the samples’ means, then get a histogram of the samples’ means.

Open RepUSSample.xls.

Add a worksheet and name it Sample. Copy the first row (with the data labels) from the Data worksheet to the Sample worksheet. Click on the 1 in gray on the left in Data, right-click the selected row, then select Copy. Click on the tab at the bottom for Sample. Right-click on A1 and select Paste.

Add another worksheet, Means. Label the first column Mean and the second column Means.

To take a sample, we will use the macro we used in Section 2.1.

Sub takeSample()
  Randomize Timer
  For SampleRow = 2 To 11
    dataRow = 2 + Fix(50000 * Rnd)
    Worksheets ("Data").Rows(dataRow).Copy
    Worksheets("Sample").Rows(SampleRow).PasteSpecial
  Next SampleRow
End Sub

In cell A2 of your Means worksheet, enter the formula to get the average of the column in Sample that you are interested in. For example, if you are interested in Education, which appears in column C, enter =average(sample!C:C) .

We will edit the macro to copy the averages from A2 to the B column. Here’s the modified macro that will do the trick (the changes are in boldface).

Sub takeSample()
  Randomize Timer
  For r = 2 To 401
    For SampleRow = 2 To 11
      dataRow = 2 + Fix(50000 * Rnd)
      Worksheets("Data").Rows(dataRow).Copy
      Worksheets("Sample").Rows(SampleRow).PasteSpecial
    Next SampleRow
    Worksheets("means").Cells(r, 2).Value = Worksheets("means").Cells(2,1)Value
  Next r
End Sub

After you run this macro (if your computer is a little slower, the macro may take some time to run) the Means column (column B) of the Means worksheet will be filled in. To get the histogram, select Tools, Data Analysis, Histogram and click OK. Select the first mean and drag down to select all of the means in your Means worksheet. Click OK. Click on the Graph button in the toolbar, then select Next and Finish.

How would you describe the distribution? What is the mean? What is the median?

By editing the macro, you can change the sample size. Does the sample size make a difference?

2. Collect a distribution of proportions that is normally distributed and check the symmetry.

Call up a workbook from one of the projects in which we took samples and recorded their proportions. For example, the workbook from the project you did in Section 3.3 has proportions from random samples. If your probability was near 0 or 100% and your sample size is small, the distribution won’t be normal. In that case, edit the macro to set the probability to 50%.

If you don’t have a copy of a workbook with proportions from random samples, go back and follow the steps to collect proportions from random samples.

Get the mean and median of one of the proportions: To get the mean, select a blank cell and type =average( , select the column of proportions, type ) (closing parenthesis), and press “Enter.” Sort the proportions (select the proportion and click on Sort Ascending) and scroll down to get the median.

Does it appear that the distribution is symmetrical? Try other sample sizes. Does sample size matter? Try other probabilities. How do sample size and probability affect the symmetry?