# LAB 5 by gegeshandong

VIEWS: 4 PAGES: 2

• pg 1
```									                        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
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

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.