Charley Tichenor
The model equation is Y = X b + e where Y is a 1-column N-row array of
observed values of the dependent variable, X is a k-column (possibly including a
column of ones for a constant term) N-row array of observations of multiple
independent/explanatory variables, b is a 1-column k-row array containing the
regression coefficients (b has as many rows as X has columns), and e is a
1-column N-row vector containing the random noise associated with each
observation.
Straight from the textbook, the estimator for the regression coefficients is
b_hat = (X' X)^-1 X' Y
The estimator for the variance of the noise (e) is
s^2 = (Y' Y - b_hat' X' Y) / (N - k)
The estimator for the variance-covariance matrix for the estimator of the
regression coefficients is
Var(b_hat) = s^2 (X' X)^-1
And the square roots of the diagonal entries in Var(b_hat) are the standard
errors of the respective regression coefficient estimators in b_hat.
There are many subtle ways to rearrange these calculations, and some involved
ways to minimize floating point rounding error. However, the formulas above
reproduce what Excel's LINEST function gives. If the Regression tool in the Data
Analysis Tool gives something different, I don't know how it's calculated.
So, with data in ranges named X and Y, standard error is given by
=INDEX((MMULT(TRANSPOSE(Y),Y)
-MMULT(MMULT(TRANSPOSE(MMULT(MINVERSE(MMULT(TRANSPOSE(X),X)),
MMULT(TRANSPOSE(X),Y))),TRANSPOSE(X)),Y))/(ROWS(X)-COLUMNS(X))
*MINVERSE(MMULT(TRANSPOSE(X),X)),{3,2,1},{3,2,1})^0.5
which gives the same result as
=INDEX(LINEST(Y,X,FALSE,TRUE),2,0)
where the first column of the X array is all ones for the constant term, and X
ontains 2 other column variables for a total of 3 columns.
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
provides formulas that do not require array formulas and are more
numerically accurate than the equations Harlan suggested. His approach
is mathematically exact, but numerically unstable.
Jerry
Charley
>.
>
Precisely! The OP asked for what Excel uses, and that's what I gave.
Mathematically exact but numerically unstable, just like Excel.