Regression Coefficient Standard Error 
Author Message
 Regression Coefficient Standard Error

Could someone please provide the formula for how Excel
calculates the regression coefficient standard error, or a
similar formula for this calculation?  Your time on this
would be appreciated.

Charley Tichenor



Sat, 30 Jul 2005 03:54:44 GMT  
 Regression Coefficient Standard Error

Quote:
"Charley Tichenor" wrote...
>Could someone please provide the formula for how Excel
>calculates the regression coefficient standard error, or a
>similar formula for this calculation?  Your time on this
>would be appreciated.

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.



Sat, 30 Jul 2005 05:03:23 GMT  
 Regression Coefficient Standard Error
http://groups.google.com/groups?selm=3BB1DAEC.3080705%40mediaone.net

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

Quote:

> Could someone please provide the formula for how Excel
> calculates the regression coefficient standard error, or a
> similar formula for this calculation?  Your time on this
> would be appreciated.

> Charley Tichenor



Sat, 30 Jul 2005 21:53:18 GMT  
 Regression Coefficient Standard Error
Thanks to both of you for your quick replies.

Charley

Quote:
>-----Original Message-----
>Could someone please provide the formula for how Excel
>calculates the regression coefficient standard error, or
a
>similar formula for this calculation?  Your time on this
>would be appreciated.

>Charley Tichenor
>.



Sat, 30 Jul 2005 22:10:59 GMT  
 Regression Coefficient Standard Error

Quote:
"Jerry W. Lewis" wrote...
>http://groups.google.com/groups?selm=3BB1DAEC.3080705%40mediaone.net

>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.

Precisely! The OP asked for what Excel uses, and that's what I gave.
Mathematically exact but numerically unstable, just like Excel.

--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.



Sun, 31 Jul 2005 02:14:40 GMT  
 Regression Coefficient Standard Error
Agreed.
Quote:

> "Jerry W. Lewis" wrote...

>>http://groups.google.com/groups?selm=3BB1DAEC.3080705%40mediaone.net

>>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.

> Precisely! The OP asked for what Excel uses, and that's what I gave.
> Mathematically exact but numerically unstable, just like Excel.

> --
> Public Service Announcement
> Don't attach files to postings in nonbinary newsgroups like this one.



Sun, 31 Jul 2005 12:45:28 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. linear regression with negative coefficient of determination?

2. Regression Coefficients

3. How to get the coefficients of a polynomial regression

4. What is my syntax error? (Regression)

5. Linest () function error while doing a regression

6. Regression/LINEST Input Range Errors?

7. Error in Excel regression analysis?

8. Standard and Non Standard Form Development

9. Transferring standard contact data from standard contact forms into contact custom forms

10. Diffrence between Office standard for students and just plain standard


 
Powered by phpBB® Forum Software © phpBB Group