Microsoft Office Tutorials and References

In Depth Information

**Calculating linear regression**

number of independent variables in a multiple-regression analysis to an expected result,

the equation of the regression line takes the form

y = m
1
x
1
+ m
2
x
2
+ … + m
n
x
n
+ b

in which
y
is the dependent variable,
x
1
through
x
n
are
n
independent variables,
m
1
through

m
n
are the coefficients of each independent variable, and
b
is a constant.

The LINEST function

The LINEST function returns the values of
m
1
through
m
n
and the value of
b
, given a known

set of values for
y
and a known set of values for each independent variable. This function

takes the form =LINEST(
known_y’s, known_x’s, const, stats
).

The
known_y’s
argument is the set of
y
-values you already know. This argument can be a

single column, a single row, or a rectangular range of cells. If
known_y’s
is a single column,

each column in the
known_x’s
argument is considered an independent variable. Similarly,

if
known_y’s
is a single row, each row in the
known_x’s
argument is considered an

independent variable. If
known_y’s
is a rectangular range, you can use only one independent

variable;
known_x’s
in this case should be a range of the same size and shape as
known_y’s
. If

you omit the
known_x’s
argument, Excel uses the sequence 1, 2, 3, 4, and so on.

The
const
and
stats
arguments are optional. If either is included, it must be a logical

constant—either TRUE or FALSE. (You can substitute
1
for TRUE and
0
for FALSE.) The default

settings for
const
and
stats
are TRUE and FALSE, respectively. If you set
const
to FALSE, Excel

forces
b
(the last term in the straight-line equation) to be
0
. If you set
stats
to TRUE, the

array returned by LINEST includes the following validation statistics:

se
1
through
se
n

Standard error values for each coefficient

Standard error value for the constant
b

se
b

Coefficient of determination

r
2

Standard error value for
y

se
y

F
statistic

F

Degrees of freedom

D
f

Regression sum of squares

ss
reg

Residual sum of squares

ss
resid

Before creating a formula using LINEST, you must select a range large enough to hold the

result array returned by the function. If you omit the
stats
argument (or set it explicitly to

FALSE), the result array encompasses one cell for each of your independent variables and