software

I would like to report the estimated variances and covariances of the
estimated coefficients from regression using excel.
How could I do that?
If you know the statistical formulas for these, write appropriate
formulas. I know Excel does linear regression and has slope and
intercept functions but not sure about multiple regression, check tools
add-ins.--
robert111
------------------------------------------------------------------------
robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=529497Standard errors of estimates (square root of variances) are given by LINEST
with the optional 4th argument set to TRUE. Covariances are not natively
available, but can be calculated from first principles.

For simple linear regression, the covariance between slope and intercept
estimates is
=-1/sqrt(1 CVx^2)
where CVx is =STDEVP(xdata)/AVERAGE(xdata)

For more general models that are linear in the unknowns, the covariance
matrix for estimates is
MINVERSE(MMULT(TRANSPOSE(xmatrix),xmatrix))*S^2
where S is the standard deviation given by LINEST with the optional 4th
argument set to TRUE. As a calculation, this can run into numerical problems
with complicated models. If you have Excel 2003, compare the variance of
estimates from this calculation with what LINEST returns; if they do not
agree, then this approach needs more numeric precision than is available in
Excel (Excel like most software uses IEEE double precision).

Jerry

quot;Moohwanquot; wrote:

gt; I would like to report the estimated variances and covariances of the
gt; estimated coefficients from regression using excel.
gt; How could I do that?
gt;

For truth in advertising, note that -1/sqrt(1 CVx^2) is the correlation, not
the covariance between slope and intercept estimates.

Jerry

quot;Jerry W. Lewisquot; wrote:

gt; Standard errors of estimates (square root of variances) are given by LINEST
gt; with the optional 4th argument set to TRUE. Covariances are not natively
gt; available, but can be calculated from first principles.
gt;
gt; For simple linear regression, the covariance between slope and intercept
gt; estimates is
gt; =-1/sqrt(1 CVx^2)
gt; where CVx is =STDEVP(xdata)/AVERAGE(xdata)
gt;
gt; For more general models that are linear in the unknowns, the covariance
gt; matrix for estimates is
gt; MINVERSE(MMULT(TRANSPOSE(xmatrix),xmatrix))*S^2
gt; where S is the standard deviation given by LINEST with the optional 4th
gt; argument set to TRUE. As a calculation, this can run into numerical problems
gt; with complicated models. If you have Excel 2003, compare the variance of
gt; estimates from this calculation with what LINEST returns; if they do not
gt; agree, then this approach needs more numeric precision than is available in
gt; Excel (Excel like most software uses IEEE double precision).
gt;
gt; Jerry
gt;
gt; quot;Moohwanquot; wrote:
gt;
gt; gt; I would like to report the estimated variances and covariances of the
gt; gt; estimated coefficients from regression using excel.
gt; gt; How could I do that?
gt; gt;

LINEST does multiple regression, as does the Regression tool in the Analysis
ToolPak. Neither gives the covariance of estimates.

Jerry

quot;robert111quot; wrote:

gt;
gt; If you know the statistical formulas for these, write appropriate
gt; formulas. I know Excel does linear regression and has slope and
gt; intercept functions but not sure about multiple regression, check tools
gt; add-ins.
gt;
gt;
gt; --
gt; robert111
gt; ------------------------------------------------------------------------
gt; robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
gt; View this thread: www.excelforum.com/showthread...hreadid=529497