|
 |
|
|
 |
  |
 |
 |
|
 |
|
|
|
The
LOGEST() function calculates a logarithmic
trend line and returns an array of values
that describe the line.
- The syntax for the function
is LOGEST (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 LOGEST 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 exponential equation
of the form: y =
bm^x where m is the
base number, x is the variable and b
is a scaling constant.
- An exponential function
of multiple variables of the form: y
= b(m1^x1)*(m2^x2)*(m3^x3)........where
m1, m2,m3 are different base numbers
and x1, x2, x3 are different variables.
The type 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
LOGEST function can find the base number
associated with an exponential expiation
of the form y = bm^x returning only one
base number m .
- For example: LOGEST({1,2,4,8,16,32},{0,1,2,3,4,5})
= 2
The LOGEST function has calculated
the base number as equal to 2 so the
associated exponential function that
describes the known values of y {1,2,4,8,16,32}
and the known values of x {0,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 1 and calculates
the base number that best describes
the data points.
How
to use the LOGEST() function:
- Type " =LOGEST( "
- 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 LOGEST function
will calculate the base numbers associated
with each variable and return them as
an array.
- For example: LOGEST({1,2,4,8},{0,1,2,3;0,1,2,3},FALSE,
FALSE) = {1;2;1}
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 = b*(m1^x1)*(m2^x2) So it
returns the result m1 = 1 , m2 = 2 and
b = 1 giving the trend line y = (1^x)*(2^x)
which explains the results and imposes
the conditions that the trend line must
be of two variables.
|
How
to use the LOGEST() function:
- Type " =LOGEST( "
- 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 LOGEST function will calculate the
base numbers assuming there are multiple
independent functions of the form y = bm^x
and will return each term in an array.
- For example: LOGEST({1,2,4,8;1,0.5,0.25,0.125;1,3,9,27},{0,1,2,3},FALSE,
FALSE) = {2,1;1/2,1;3,1}
Where the first function
takes the form y = 1*(2^x)
the second y = 1*((1/2)^x) and the third
y = 1*(3^x)
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: LOGEST({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 = 2*(1^x)*(1^x)*(2^x) as m1 = 1, m2=
1, m3= 2 and b = 2.When the regression analysis statistics
are included they are returned in 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}
Note: The
y values here are perfect data points
for the function y = 2^x when analyzed
with the regression analysis tools the
error values can be seen to be very small.
It real empirical data the errors, deviations
and variation values will be greater an
more "realistic" as this perfect
model is being used as a familiar mathematical
example.
How
to use the LOGEST() function:
- Type " =LOGEST( "
- 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.
|
|
|
The "stats" argument sets the
option of weather or not additional regression
statistics are returned in addition to
the trend line data. 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
LOGEST function returns a mathematical
description of the exponential 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 GROWTH function. See [The
GROWTH function knoweledgebase].
Note: The
LOGEST and LINEST functions
use similar regression techniques to determine
m values. However LINEST is linear in nature
and LOGEST exponential. Calculated
error values should be compared only to
the logarithm of calculated m values. As
the regression analysis is performed using
the nature of logarithms to create a linear
model (ln y = x1ln m1 + ...xn ln mn +lnb)
and then uses the LINEST function to fit
the curves.
The LOGEST function uses a modified form
of the LINEST function algorithm and does
not actually find the least squares fit
of the exponential trend line that it fits
to the data points. Instead it finds the
least squares fit of the linear model Log
y = xlogm1 + c2logm2 + this is not strictly
the most statistically sound method of
finding the exponential trend line and
will introduce significant errors to your
results if you do not pay close attention
to regression analysis results. |
|
 |
 |
 |
|
