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.7343E-05

Residual

4

0.6

0.15

Total

5

53.5

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

71.95

1.052971984

68.330403

3E-07

69.026475

74.87352

Age (months)

0.383333333

0.020412415

18.7794214

5E-05

0.32665927

0.440007

RESIDUAL OUTPUT

Observation

Predicted Height (cm)

Residuals

1

85.75

0.25