
Linear Regression with EXCEL 






















































Find the Least Squares Regression Line with EXCEL: 






















Investigate the relationship between the height and age of children: 






















Billy's parents are concerned that he seems short for his age. Their 

pediatrician has the following record of Billy's height: 





















































Age (months) 
Height (cm) 




























36 
86 




























48 
90 




























51 
91 




























54 
93 




























57 
94 




























60 
95 

























































We would like to answer the following questions: 



























1. What is the rate of growth per month for Billy? 






















2. How tall will Billy be at 8 years of age? 






















3. Can we use this information to predict his height at age 20? 
















































































































1. Perform linear regression analysis: 






















































Input the values for the Age in Column A and 
























A 
B 
Height in Column B, then: 






















1 
Age (months) 
Height (cm) 
a. Click on Tools. 





















2 
36 
86 
b. Select Data Analysis. 





















3 
48 
90 
c. Select Regression and click OK. 





















4 
51 
91 
d. Input Y Range: highlight B1:B7, the column 





















5 
54 
93 
with the height including the label. 





















6 
57 
94 
e. Input X Range: highlight A1:A7, the column 























7 
60 
95 
with the age including the label. 


























f. Check on labels since you included the labels in 
























the highlighted portion of the range. 
























g. Click on Confidence Level, if you need this 


























information. 






















































































h. Output Range: If you decide to put 
























the output below the data, use: A10:E25 and the 


























summary output will appear below your data. 
























To place the output on a new sheet, 


























click on New Worksheet Ply. 


























i. Click on Residuals to get the residual for each 


























data value. 


























j. Click on Line Fit Plots if you want the 


























regression line drawn onto the graph. 





















































The output created by EXCEL is shown below: 




















































SUMMARY OUTPUT 



























































Regression Statistics 





























Multiple R 
0.994376713 




























R Square 
0.988785047 




























Adjusted R Square 
0.985981308 




























Standard Error 
0.387298335 




























Observations 
6 


























































ANOVA 






























df 
SS 
MS 
F 
Significance F 
























Regression 
1 
52.9 
52.9 
352.67 
4.7343E05 
























Residual 
4 
0.6 
0.15 


























Total 
5 
53.5 


























































Coefficients 
Standard Error 
t Stat 
Pvalue 
Lower 95% 
Upper 95% 























Intercept 
71.95 
1.052971984 
68.330403 
3E07 
69.026475 
74.87352 























Age (months) 
0.383333333 
0.020412415 
18.7794214 
5E05 
0.32665927 
0.440007 

















































































































RESIDUAL OUTPUT 



























































Observation 
Predicted Height (cm) 
Residuals 



























1 
85.75 
0.25 



























2 
90.35 
0.35 



























3 
91.5 
0.5 



























4 
92.65 
0.35 



























5 
93.8 
0.2 



























6 
94.95 
0.05 













































































































































































































































2. Interpret the Summary Output 













































a. Rate of Change: 





























What is the rate of growth per month for Billy? 






















The growth rate per month is expressed by the slope which can be found in this part of the output: 





















































Coefficients 
Standard Error 
t Stat 
Pvalue 
Lower 95% 
Upper 95% 























Intercept 
71.95 
1.052971984 
68.330403 
3E07 
69.026475 
74.87352 























Age (months) 
0.383333333 
0.020412415 
18.7794214 
5E05 
0.32665927 
0.440007 





















































The slope is found under Coefficients by Age (months), which is m = 0.3833. 






















This means that Billy grows .38 cm per month. 


















































































b. Find the Equation of the Least Squares Regression Line: 






















How tall will Billy be at 8 years of age? 






















To determine the height of Billy at age 8, we need to find the equation of the least squares 






















regression line. 




















































The equation is of the form: 






















y (pred) = mx + b, where m is the slope, b is the yintercept of the line and x is the age in months. 




















































We know from above that m = 0.3833. The yintercept can be found in the above output under 






















Coefficients by Intercept, which is 71.95. So, the equation of the least squares regression line is: 






















y (pred) = 0.3833 x + 71.95. 




















































We can now predict the height of Billy at age 8 by substituting x into the equation. 






















To do this we will need to convert the age of 8 years to months: 8 years = 96 months. 






















Then substitute x = 96 into the equation y = 0.3833 x + 71.95: 






















y (pred) = 0.3833 ( 96) + 71.95 = 108.75 cm 













































Hence, Billy will be 108.75 cm tall at age 8. 











































































c. Making Predictions: 






















Can we use this information to predict his height at age 20? 






















In general, the above equation can be used to make predictions. However, the result has to be 






















examined for feasibility. Let's substitute x = age into the equation. Again we will have to convert 






















20 years to months. 



























































So, x = 20(12) = 240 months. 






















y (pred) = 0.3833 (240) + 71.95 = 163. 94 cm 













































Hence, Billy will be 163.94 cm at age 20, which is a feasible result. 


























































































































































































































d. Find Correlation Coefficient and Coefficient of Determination: 




















































Regression Statistics 





























Multiple R 
0.994376713 




























R Square 
0.988785047 




























Adjusted R Square 
0.985981308 




























Standard Error 
0.387298335 




























Observations 
6 



















































Multiple R: is equivalent to r, the correlation coefficient. 






















R Square: is equivalent to R^{2}, the coefficient of determination. 





























