Data Matters with Excel®

Activity 8.3

A scatter plot is like a statistic. When a researcher gathers data and summarizes them in a scatter plot, there are similarities with what happens when that researcher summarizes the data with proportions and averages.

To see this, in this project you will take samples from the representative U.S. sample and get scatter plots from those samples. As you replace the samples, you will somewhat see how random sampling affects scatter plots.

Open the RepUSSample.xls workbook that you used in several previous projects. You will run a macro to take a sample onto the Sample worksheet. Before you run the macro, make sure the labels from row 1 of the Data worksheet appear on row 1 of the Sample worksheet.

Here’s the macro. It is probably still in your workbook.

Sub Sampling()
  Randomize Timer
  For SampleRow = 2 To 21 ‘Set your total sample size by changing 21.
    dataRow = 2 + Fix(50000 * Rnd)
    Worksheets("Data").Rows(dataRow).Copy
    Worksheets("Sample").Rows(SampleRow).PasteSpecial
  Next SampleRow
End Sub

Your new worksheet is going to have a copy of the cells in Sample and a graph. If you put the graph on the Sample worksheet, it botches things up. In A1 of your new worksheet, enter the formula =Sample!A1 . Select and drag that formula to the right to column L. Select and drag down to row 21.

Click on the chart wizard button at the top. Select XY (Scatter), click on Next, Series, Add. Then click inside the X Values box and select the data values for the x-axis. Delete everything from the Y Values box and select the data values for the y-axis. Click on Finish.

Right-click one of the dots on the scatter plot and select Add Trendline, OK. That adds a line that is a pretty good summary of the linear pattern in the scatter plot.

You need to run the macro several times. To make this easy, select Tools, Macro, Macros. Select the Sampling macro and click on Options. In the Shortcut Key box, type z . Click OK. Now when you press “Ctrl-Z,” the sample is replaced with a new random sample. Try it and watch the scatter plot.

Run the macro quite a few times. See what happens to the scatter plot. Try other pairs of variables.

Try increasing the sample size to 200. To increase the sample, edit the macro so that it fills to row 201. Then, in your new worksheet, delete the graph, select a row, and drag its equations down to row 201. Repeat the steps to add another graph, this time selecting the data all the way down to row 201.

Try taking many samples. How does the increased sample size influence the effect that random sampling has on the scatter plot?

What is the relationship between the image of a sample’s data in a scatter plot and the image of an entire population’s data?