316 Statistical Methods
is as small as possible. This procedure is called the least-squares method.
The values a and b that result in the smallest possible sum for the squared
residuals can be calculated from the following formulas:b 5an
i 51(^1) xi 2 x (^21) yi 2 y 2
a
n
i 511 xi^2 x^2
2
a 5 y 2 bx
These are called the least-squares estimates. For example, say our data
set contains the values listed in Table 8-1:
Table 8-1 Data for Least-Squares Estimates
xy
13
24
13
34
25
The sample averages for x and y are 1.8 and 3.4, and the estimates for a
and b are
b 5
a
n
i 51
(^1) xi 2 x (^21) yi 2 y 2
a
n
i 511 xi^2 x^2
2
5
(^112) 1.8 (^2132) 3.4 (^21122) 1.8 (^2142) 3.4 21 c 1122 1.8 (^2152) 3.4 2
(^112) 1.8 (^221122) 1.8 221 c 1122 1.8 22
50 .5
a 5 y 2 bx
53 .4 20 .5 3 1. 8
52 .5
Thus the least-squares estimate of the regression equation is y 52 .5 10 .5x.
Regression Functions in Excel
Excel contains several functions to help you calculate the least-squares
estimates. Two of these are shown in Table 8-2.