Data Matters with Excel®

Activity 9.3

Now that there is computer software to do all the computational steps of calculating a regression equation, very few people calculate regression equations by hand. You might wonder whether the calculations in Section 9.3 of Data Matters (or your calculations for the exercises) are correct. To double-check them, you can enter the data into Excel and compare your answers with Excel’s.

Once your data are entered in a worksheet, you get a regression model two different ways. One way is to get a scatter plot, right-click on one of the dots, and select Add Trendline. Click on Options and select Display Equation on Chart. That’s what you did in the project in Section 9.1.

Another way is to click on Tools, Data Analysis. Select Regression and click OK. Click on the small red arrows and select your data to indicate the locations of the y-values, and x-values. Click OK.

Part of the output will be a table like this.

The slope is the coefficient for the x variable, and its standard error is just to the right.

Here are the steps for this part of the project.

1. Find the slope of a relationship between two numeric variables in the population.
2. Take a sample.
3. Calculate the regression equation and standard error.
4. Check and record whether the confidence interval based on the sample’s slope included the population’s slope.

Repeat the steps to check a number of confidence intervals for the population’s slope.

Step 1: Find the slope of a relationship between two numeric variables in the population.

Open the workbook you used in the project in Section 8.3. Then follow the directions above to find the slope in the population.

Step 2: Take a sample.

Your workbook has a macro associated with “Ctrl-Z” that took new samples.

Step 3: Calculate the regression equation and standard error.

Once you have a new sample, follow the directions above to get the slope and standard error.

Step 4: Check and record whether the confidence interval based on the sample’s slope included the population’s slope.

Add and subtract two standard errors to the sample’s slope. Did your sample include the population’s slope? Record the slope, its standard error, and the confidence interval.

Repeat these steps to gather more slopes. How often did your confidence interval include the population’s slope?

Try different sample sizes. Does the sample size influence how well the confidence interval works?

How does the standard deviation of the samples’ slopes compare with the estimated standard error of the slope you got in your first sample?