Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get covariance of the estimated coefficients using excel
I would like to report the estimated variances and covariances of the
estimated coefficients from regression using excel. How could I do that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get covariance of the estimated coefficients using excel
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: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529497 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get covariance of the estimated coefficients using excel
Standard 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 "Moohwan" wrote: I would like to report the estimated variances and covariances of the estimated coefficients from regression using excel. How could I do that? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get covariance of the estimated coefficients using exce
For truth in advertising, note that -1/sqrt(1+CVx^2) is the correlation, not
the covariance between slope and intercept estimates. Jerry "Jerry W. Lewis" wrote: Standard 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 "Moohwan" wrote: I would like to report the estimated variances and covariances of the estimated coefficients from regression using excel. How could I do that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get covariance of the estimated coefficients using exce
LINEST does multiple regression, as does the Regression tool in the Analysis
ToolPak. Neither gives the covariance of estimates. Jerry "robert111" wrote: 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: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529497 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
VBA for Excel 2000 file is corrupt | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |