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 4 Go to A128 and enter slope Go to A129 and enter intercept 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 Fill in the array known y s with C2 C79 Fill in the array known x s with B2 B79 and click on OK The slope is shown in C128 Go to C129 and enter get the function intercept from the toolbar Fill in the array known y s with C2 C79 Fill in the array known x s with B2 B79 and click on OK The intercept is shown in C129 Go to D128 enter get the slope function from the toolbar Fill in the array known y s with D80 D124 Fill in the array known x s with B80 B124 and click on OK The slope is shown in D128 Go to D129 and enter get the function intercept from the toolbar Fill in the array known y s with D80 D124 Fill in the array known x s with B80 B124 and click on OK The intercept is shown in D129 Fill in the slope and intercept for the prediction equation for the old furnace 5 6 7 8 9 10 11 12 13 14 15 16 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 19 20 Now we will ask Excel to plot these least squares regression equations Go the the scatterplot which shows gas use for both furnaces 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 22 23 24 25 26 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 Repeat the procedure for the new furnace data Move the equation info to the lower left corner of the plot 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 Does the residual plot show any systematic pattern It appears safe to use the regression lines for prediction Predict the gas use for both furnaces when total temperature 100 You can do this with your calculator or with excel 1 27 28 29 30 Old furnace prediction 2 New furnace prediction Print the scatterplot showing the regression lines for old and new furnaces Also print the residual plot for the new furnace data Print this sheet with your answers to turn in with the two graphs Save your file
View Full Document
Unlocking...