Calculation

  • Thread starter Thread starter Seifder
  • Start date Start date
S

Seifder

Does anyone know the detailed calculation steps behind the
TREND function in Excel? I know it has to do with method
of least squares.
 
See help files in Excel on TREND function. This will refer you to the LINEST function for details.

Mark Graesser

----- Seifder wrote: -----

Does anyone know the detailed calculation steps behind the
TREND function in Excel? I know it has to do with method
of least squares.
 
TREND() does use the method of least squares.

The details of calculation (prior to Excel 2003) are that the
coefficients returned by LINEST() are calculated as
b = (X' * X)^-1 * X' * y
where y is the vector of known_y's, X is the matrix of known_x's
supplemented with a column of ones, unless const is specified as False,
X' is the transpose of X, ^-1 is matrix inversion, and all
multiplications are matrix multiplications.

TREND() then returns
Xn * b
where Xn is the matrix of new_x's, supplemented with a column of ones,
unless const is specified as False.

Excel 2003 uses singular value decomposition of X to permit calculation
of b with much less cancellation than is involved in forming
(X' * X)^-1 * X' * y
just as DEVSQ(data)/COUNT(data) is a numerically better way to calculate
than VARP(data) in versions prior to Excel 2003
http://groups.google.com/[email protected]

Jerry
 
Back
Top