Stat Lab

Only select cars manufactured within the last 10 years.

1. Would you expect a positive or negative correlation between the model year and mileage?

2. Is mileage the independent or dependent variable?

EXCEL PROCEDURES:

1. In Excel, type “Year” in cell A1 and “Mileage” in cell B1. Enter the data you collected from the above table in columns A and B respectively.

2. Have Excel calculate the linear correlation coefficient (r), the coefficient of determination (r2), and the test statistic (t) for you:

a. In cell D1, type “r”.

b. In cell D2, type “r-square”.

c. In cell D3, type “n”.

d. In cell D4, type “d.f.”.

e. In cell D5, type “t”.

f. In cell E1, find the correlation coefficient (r) by select the Formulas tab and clicking on the More Functions icon and selecting Statistical. Under the statistical menu select CORREL. You will see the Function Arguments dialog box open. In the Function Arguments dialog box select cells A2 through A21 for Array1 and select cells B2 through B21 for Array2. Click OK. The correlation coefficient will now be displayed.

g. In cell E2, find the coefficient of determination (r2) by using the formula =E1^2.

h. In cell E3, find the sample size (n) by using the formula =COUNT(A2:A21).

i. In cell E4, find the degrees of freedom (d.f.) by using the formula =E3-2.

j. In cell E5, find the test statistic (t) by using the formula =(E1)/SQRT((1-E2)/(E4)).

k. Copy these cells to the end of this lab.

3. Have Excel produce a scatterplot for you:

a. Click on cell A1 and drag through cell B21 to select the values to be graphed.

b. In the Insert tab, click on Scatter in the Charts menu.

c. Select the first chart in the Scatter menu.

d. Click OK and your scatterplot will appear.

4. Have Excel add a linear regression line to the scatterplot you constructed above:

a. Click anywhere in the chart region of your scatterplot and notice that a new Chart Tools tab is added to the ribbon at the top of Excel.

b. Click on the Quick Layout tab at the top left and then click on Layout 9.

c. Click on the legend labels to the right of the plot area and delete the legend.

d. Edit the both axis labels with the appropriate data variables.

e. Click on the equation and move it to a location on the graph outside of the plot area so the equation is visible.

f. Right click on the equation and choose Format Treadline Label. Then change ‘general’ to ‘number’ so the equation is no longer in scientific notation.

g. Edit the chart title to include your name and “Car Mileage/Year”.

h. Right click on the graph and select Copy.

i. Copy the scatterplot to the end of this lab.

ANALYSIS:

1. What is your regression equation for Year of Car predicting Mileage? Take time to confirm your equation from Excel with the equation that you get from your TI-83/84.

2. Identify the values for the following from your Excel printout (these should also match TI-83/84 output):

a. Slope of regression line:

b. y-intercept of regression line:

c. Coefficient of determination (r2):

d. Linear correlation coefficient (r):

3. Does the value of the linear correlation coefficient (r) agree with the strength and direction of your scatter plot? Explain.

4. Determine if there is a significant negative linear correlation at the 0.01 level of significance.

a. State the null and alternate hypotheses to test for a significant negative linear correlation.

b. Determine the critical value and sketch (or describe) the rejection region.

c. Record the test statistic (t) from your Excel printout.

d. Compare the t values in part c to the critical value(s) in part b and make a decision to reject or not reject the null hypothesis. Interpret the results in context.

5. Based on your conclusion from #4, is this regression equation appropriate for predicting mileage? If so, for which years is it appropriate?

6. Use the regression equation to predict the mileage of your own personal car:

a. What year is your car?

b. How many miles are on your car?

c. If appropriate, use the regression equation from above to predict the mileage on your car based upon the year it was made.

Linear Regression Page 1

7. Interpret the coefficient of determination (r2) in context. See Unit 11 for examples.

Lab 12 Page 5