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
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
Search JabSto ::

Custom Search