|
 |
|
|
 |
  |
 |
 |
|
 |
|
|
|
The
LINEST() function calculates a linear trend
line and returns an array of values that
describe the line.
- The syntax for the function
is LINEST (known_y's, known_x's,
const, stats).
The
arguments "known_y's" and "known_x's" can
be numerical values, cells references,
arrays or named ranges .The "known_y's"
are the known y values. The
arguments "const" and "stats"
must have logical values ie "TRUE" or "FALSE".
Note: Array formulas
can not be entered by typing brackets into
the formula bar. The array must be entered
using the array entry keyboard command. (ie.
Ctrl + Shift + Enter).
The LINEST function
uses the known_y's"
and known_x's to calculate a trend line
that best describes the data. The
function then returns a series of predicted
x values as an array. There are two possible
forms in which the known_y's and known_x's
are analyzed.
- A single variable that
is fitted to the linear equation
of the form: y =
mx + b where m is the
slope of the line, x is the variable
and b is the intercept with the y axis.
- A linearly function
of multiple variables of the form: y
= m1x1 + m2x2 + m3^x3......+b
where m1, m2,m3 are slopes, x1, x2, x3
are different variables and b is the
y intercept.
The form of analysis
that is performed depends on the form of
the argument in
"known_y's" and "known_x's".
If the known values of y and x are entered
as a single column array then each value
is considered to be a known value of a
function with a single variable (type 1).
If the known values of y and x are entered
as an array of multiple columns than each
column is considered to be known values
of a different variable in the function
of type2.
In its simplest use the
LINEST function can find the slope
associated with an linear expiation
of the form y =mx +b where b is set to
zero returning only the value of the
slope m.
- For example: LOGEST({2,4,6,8,10},{1,2,3,4,5})
= 2
The LINEST function has calculated
the slope as equal to 2 so the
associated linear function that
describes the known values of y {2,4,6,8,10}
and the known value of x {1,2,3,4,5}
is the function:
Leaving the "const"
and "stats" omitted
sets them both to false. The function
then sets the value of b to 0 and calculates
the slope that best describes
the data points.
How
to use the LINEST() function:
- Type " =LINEST( "
- Enter
the coordinates of the "known_y's"
data cells "A2:A7"
- Type a comma.
- Enter
the coordinates of the "known_x's",
"B2:B7".
- Type ")" then
press the "Enter" key.
|
|
|
When an array of known
x values is entered or a multiple column
array of known y values the LINEST function
will calculate the slopes associated
with each variable and return them as
an array.
- For example: LINEST({2,4,6,8,10},{0,1,2,3,4,5;0,1,2,3,4,5},FALSE,
FALSE) = {0;2;0}
The single column of
y values and the double column of x values
the function assumes there are multiple
x variables associated with each known
y value.
That is the function must take
the form: y = (m1x1) + (m2x2) +b So it
returns the result m1 = 0 , m2 = 2 and
b = 0 giving the trend line y = (2^x)
.
|
How
to use the LINEST() function:
- Type " =LINEST( "
- Enter
the coordinates of the "known_y's"
data cells "A2:A7"
- Type a comma.
- Enter
the coordinates of the "known_x's",
"B2:B7".
- Type ")" then
press the "Enter" key.
|
|
|
|
When
an array of multiple columns of known y
values is entered and a single column array
of known x values the LINEST function will
calculate the base numbers assuming there
are multiple independent functions of the
form y = mx+b and will return each term
in an array.
- For example: LINEST({1,2,3,4;4,6,8,10;3,6,9,12},{0,1,2,3}TRUE,
FALSE) = {1,0;2,2;3,0}
Where the first function
takes the form y = x
the second y = 2x + 2 and the third
y = 3x
With each of these different set ups if
the "stats" value
is set to "TRUE" the function
also returns a set of regression analysis
statistics to accompany the calculated
base number values.
- e.g. For example: LINEST({2,4,8,16,32},{0,1,2,3,4;
0,1,2,3,4; 0,1,2,3,4}) = {1;1;2;2}
This gives a single function
of 3 variable to describe the y values
{2,4,8,16,32}: y = (1x) + (1x)+(2x) as
m1 = 1, m2= 1, m3= 2 and b = 2.When the
regression analysis statistics are included
they are returned into the same array,
a column of associated statistics for
each returned value. ie LOGEST({2,4,8,16,32},{0,1,2,3,4;
0,1,2,3,4; 0,1,2,3,4},FALSE,TRUE) = {1,0,1,1.58E+32,4.8
;1,0,1.7E-16,3,9.12E-32;2,5.5E-17;2,1.35E-36}
How
to use the LINEST() function:
- Type " =LINEST( "
- Enter
the coordinates of the "known_y's"
data cells "A2:A7"
- Type a comma.
- Enter
the coordinates of the "known_x's",
"B2:B7".
- Type a comma.
- Type "FALSE".
- Type a comma.
- Type "TRUE".
- Type ")" then
press the "Enter" key.
|
|
|
If set to "TRUE" he
regression statistics are presented as
a series of values specific to each variable
i.e.
m1 |
mn-1 |
..... |
mn |
b |
se |
se n-1 |
..... |
se1 |
seb |
r |
sev |
|
|
|
F |
df |
|
|
|
ssreg |
ssresid |
|
|
|
The regression analysis statistics can
help decide how useful the calculated
trend line will be as a model of existing
values or in predicting future values
or "forecasting" results.
.
mn |
The base number for
the variable xn |
| sen |
Gives the standard
error value for each coefficient |
| r2 |
The coefficient of
determination |
sey |
Standard error for y |
F |
Gives the F statistic |
df |
Gives the degrees of freedom |
ssreg |
The regression sum of squares |
| ssresid |
The residual sum of squares |
The
LINEST function returns a mathematical
description of the linear trend
line that fits data points. It does NOT
predict values for y that are outside
of the known y values. To extrapolate
(predict) unknown y values
use the FORCAST function. See [The
FORCAST function knoweledgebase].
Using Linear
Regression Statistics
|
|
 |
 |
 |
|
