LAB 5 by gegeshandong

VIEWS: 4 PAGES: 2

									                        LAB 5 INSTRUCTIONS FOR EXCEL 2007
                                LINEAR REGRESSION

NAME________________________LAB TIME________LAB BLDG________________

1.    Open your Lab 4 file on Scatterplots and Correlation. If you did not save your lab 4 file,
      go to the Stat 301T webpage, click on Lab Data & Questions and open the Lab4 data file
      and the Lab4INSTR file. Construct the scatterplot between temperature and old furnace.
      Also construct the scatterplot between temperature and both furnaces.

2.    We will now calculate the slope and intercept for the least squares regression line for the
      relationship between temperature and old furnace gas use.

3.    Go to A128 and enter “slope”. Go to A129 and enter “intercept”.
4.    Go to C128, enter = , go to toolbar and get the function “slope”. If it is not listed, use
      “more functions” to get access to the entire list.
5.    Fill in the array “known y’s” with C2:C79.
6.    Fill in the array “known x’s” with B2:B79 and click on OK. The slope is shown in C128.
7.    Go to C129 and enter =, get the function “intercept” from the toolbar.
8.    Fill in the array “known y’s” with C2:C79.
9.    Fill in the array “known x’s” with B2:B79 and click on OK. The intercept is shown in
      C129.
10.   Go to D128, enter = , get the slope function from the toolbar.
11.   Fill in the array “known y’s” with D80:D124
12.   Fill in the array “known x’s” with B80:B124 and click on OK. The slope is shown in
      D128.
13.   Go to D129 and enter =, get the function “intercept” from the toolbar.
14.   Fill in the array “known y’s” with D80:D124.
15.   Fill in the array “known x’s” with B80:B124 and click on OK. The intercept is shown in
      D129.
16.   Fill in the slope and intercept for the prediction equation for the old furnace:

      Old furnace weekly gas use = ________ + ________X(week’s total morning
      temperature)

17.   Fill in the slope and intercept for the prediction equation for the new furnace:


      New furnace weekly gas use = ________ + ________X(week’s total morning
      temperature)

18.   Now we will ask Excel to plot these least squares regression equations.
19.   Go the the scatterplot which shows gas use for both furnaces.
20.   Right click on any old furnace data symbol, click on “add trendline”, accept the default
      type “linear”, click on “display equation on chart” , click on “display R-squared value on
      chart”, click on Close.
21.   The “best fit” line for the old furnace appears on the scatterplot along with the equation
      and the value of R-squared. Click on the equation info and move it to the upper right
      corner of the plot.
22.   Repeat the procedure for the new furnace data. Move the equation info to the lower left
      corner of the plot.
23.   The two regression lines appear to be good fits for the data. Nevertheless we will
      generate a residual plot for the new furnace data to check for non-random patterns in
      the residuals. This is an important diagnostic technique.
      1)      Go to the toolbar, click on Data>Data Analysis, select Regression and click OK.
      2)      Fill in Input Y Range: D80:D124
      3)      Fill in Input X Range: B80:B124
      4)      Click Output Range and fill in F81
      5)      Click on Residuals and Residual Plots
      6)      Click on Line Fit Plots and click on OK
      7)      Excel gives various data on the regression, lists residuals, plots the residuals and
              plots the best fit regression equation.
24.   Does the residual plot show any systematic pattern? ______________
25.   It appears safe to use the regression lines for prediction.
26.   Predict the gas use for both furnaces when total temperature = 100. You can do this with
      your calculator or with excel.

      1)     Old furnace prediction = _________

      2)      New furnace prediction = _________
27.   Print the scatterplot showing the regression lines for old and new furnaces.
28.   Also print the residual plot for the new furnace data.
29.   Print this sheet with your answers to turn in with the two graphs.
30.   Save your file.

								
To top