|
| |
LINEST*
Calculates the statistics for a line by using the "least
squares" method to calculate a straight line that best fits your data, and
returns an array that describes the line. Because this function returns an array
of values, it must be entered as an array formula.
The equation for the line is:
y = mx + b or y = m1x1 + m2x2 + ... + b (if there are multiple ranges
of x-values)
where the dependent y-value is a function of the independent x-values.
The m-values are coefficients corresponding to each x-value, and b is a constant
value. Note that y, x, and m can be vectors. The array that LINEST returns is
{mn,mn-1,...,m1,b}. LINEST can also return additional regression statistics.
Syntax
LINEST(known_y's,known_x's,const,stats)
Known_y's is the set of y-values you already know in the
relationship y = mx + b.
- If the array known_y's is in a single column, then each column of
known_x's is interpreted as a separate variable.
- If the array known_y's is in a single row, then each row of
known_x's is interpreted as a separate variable.
Known_x's is an optional set of x-values that you may
already know in the relationship y = mx + b.
- The array known_x's can include one or more sets of variables. If
only one variable is used, known_y's and known_x's can be ranges of any
shape, as long as they have equal dimensions. If more than one variable is
used, known_y's must be a vector (that is, a range with a height of one row
or a width of one column).
- If known_x's is omitted, it is assumed to be the array {1,2,3,...}
that is the same size as known_y's.
Const is a logical value specifying whether to force the
constant b to equal 0.
- If const is TRUE or omitted, b is calculated normally.
- If const is FALSE, b is set equal to 0 and the m-values are
adjusted to fit y = mx.
Stats is a logical value specifying whether to return
additional regression statistics.
- If stats is TRUE, LINEST returns the additional regression
statistics, so the returned array is
{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
- If stats is FALSE or omitted, LINEST returns only the
m-coefficients and the constant b.
The additional regression statistics are as follows.
|
Statistic
|
Description
|
|
se1,se2,...,sen
|
The standard error
values for the coefficients m1,m2,...,mn.
|
|
Seb
|
The standard error
value for the constant b (seb = #N/A when const is FALSE).
|
|
r2
|
The coefficient of
determination. Compares estimated and actual y-values, and ranges in value
from 0 to 1. If it is 1, there is a perfect correlation in the sample —
there is no difference between the estimated y-value and the actual
y-value. At the other extreme, if the coefficient of determination is 0,
the regression equation is not helpful in predicting a y-value. For
information about how r2 is calculated, see "Remarks" later in
this topic.
|
|
sey
|
The standard error
for the y estimate.
|
|
F
|
The F statistic, or
the F-observed value. Use the F statistic to determine whether the
observed relationship between the dependent and independent variables
occurs by chance.
|
|
df
|
The degrees of
freedom. Use the degrees of freedom to help you find F-critical values in
a statistical table. Compare the values you find in the table to the F
statistic returned by LINEST to determine a confidence level for the
model.
|
|
ssreg
|
The regression sum
of squares.
|
|
ssresid
|
The residual sum of
squares.
|
The following illustration shows the order in which the additional
regression statistics are returned.
| |
A |
B |
C |
D |
E |
F |
| 1 |
mn |
mn-1 |
... |
m2 |
m1 |
b |
| 2 |
sen |
sen-1 |
... |
se2 |
se1 |
b |
| 3 |
R2 |
sey |
|
|
|
|
| 4 |
F |
df |
|
|
|
|
| 5 |
sereg |
ssresid |
|
|
|
|
*from Microsoft Excel 2000 help
Back to Project 3
|