Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default COnfidence Intervals in Multiple Regression

Is there a way to get a confidence interval about your predicted values for
your dependent variable (Y) in Excels multiple regression?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,696
Default COnfidence Intervals in Multiple Regression

May want to look at this template...

http://office.microsoft.com/en-us/ma...681821033.aspx

"FredZack" wrote:

Is there a way to get a confidence interval about your predicted values for
your dependent variable (Y) in Excels multiple regression?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default COnfidence Intervals in Multiple Regression

Take a look at Regression in the Analysis ToolPak. There is also a
CONFIDENCE fucntion. The ATP can be attached by choosing Tools, Add-ins and
checking Analysis ToolPak in 2003 or earlier.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"FredZack" wrote:

Is there a way to get a confidence interval about your predicted values for
your dependent variable (Y) in Excels multiple regression?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default COnfidence Intervals in Multiple Regression

Not directly. The ATP regression tool merely reformats and elaborates on the
output of LINEST. LINEST gives standard errors of regression coefficients,
which can be used to construct t-tests to test the significance of a given
coefficient, but which are useless for confidence intervals on predicted
values, because no native excel functions directly give the covariances
between these parameter estimates.

The Multiple Regression template by Business Spreadsheets adds a few things
beyond what LINEST gives, but standard errors and/or confidence intervals for
predicted values are not among them.

Excel's CONFIDENCE function has no bearing on your question. Moreover, it
is rarely useful for anything at all, because it presumes that you know the
true underlying standard deviation instead of having to estimate it from data.

If you are comfortable with linear algebra, you can "roll your own"
solution, however. If X is the model matrix (including a column of 1's for
the mean term) for the known y values, and v is the analogous row of
predictors for single point to be predicted, then if there are not numerical
issues, then
=MMULT(v,MMULT(MINVERSE(MMULT(TRANSPOSE(X),X)),MMU LT(TRANSPOSE(X),y)))
should agree with
=TREND(y,X,v)
Excel versions since 2003 use a mathematically equivalent but numerically
superior calculation. If the two calculatiosn do not agree, the the
following confidence interval calculation will not be trustworthy. Assuming
that they agree, then
=SQRT(MMULT(MMULT(v,MINVERSE(MMULT(TRANSPOSE(X),X) )),TRANSPOSE(v)))*S
(where S is the sey estimate from the LINEST output) is the standard error
of the fitted multiple regression at the predictors v. If you are predicting
a future value instead of just the fitted mean, use
=SQRT(1+MMULT(MMULT(v,MINVERSE(MMULT(TRANSPOSE(X), X))),TRANSPOSE(v)))*S
A 1-alpha level confidence interval is then
=prediction +/- TINV(alpha,df)*SE
so that alpha=0.05 would give a 95% confidence interval.

Jerry

"FredZack" wrote:

Is there a way to get a confidence interval about your predicted values for
your dependent variable (Y) in Excels multiple regression?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default COnfidence Intervals in Multiple Regression

Here are a few clarifications of my previous relply. The details of my reply
presumed some subtle unstated aspecs of the linear algebra formulation of the
regression ... sorry

If X is the model matrix augmented by a column of 1's for the mean, as
presumed in my MMULT formulas, then you would also need Xm, the same matrix
without the column of 1's. The TREND formula for comparison, then would be
either
=TREND(y,Xm,v)
or equivalently
=TREND(y,X,v,FALSE)
The degrees of freedom, df, and the sey estimate, S, would be from
=LINEST(y,Xm,,TRUE)

Also, while I said that Excel's CONFIDENCE function has no bearing on your
question, I did not say why. The reason is that it deals with the confidence
interval for a simple population mean, not a regression of any kind. As I
did state, it is rarely useful even for its intended purpose, as it presumes
that you are using information that you almost never will have (although it
can be a decent approximation in that context if the sample size is large).

Jerry

"Jerry W. Lewis" wrote:

Not directly. The ATP regression tool merely reformats and elaborates on the
output of LINEST. LINEST gives standard errors of regression coefficients,
which can be used to construct t-tests to test the significance of a given
coefficient, but which are useless for confidence intervals on predicted
values, because no native excel functions directly give the covariances
between these parameter estimates.

The Multiple Regression template by Business Spreadsheets adds a few things
beyond what LINEST gives, but standard errors and/or confidence intervals for
predicted values are not among them.

Excel's CONFIDENCE function has no bearing on your question. Moreover, it
is rarely useful for anything at all, because it presumes that you know the
true underlying standard deviation instead of having to estimate it from data.

If you are comfortable with linear algebra, you can "roll your own"
solution, however. If X is the model matrix (including a column of 1's for
the mean term) for the known y values, and v is the analogous row of
predictors for single point to be predicted, then if there are not numerical
issues, then
=MMULT(v,MMULT(MINVERSE(MMULT(TRANSPOSE(X),X)),MMU LT(TRANSPOSE(X),y)))
should agree with
=TREND(y,X,v)
Excel versions since 2003 use a mathematically equivalent but numerically
superior calculation. If the two calculatiosn do not agree, the the
following confidence interval calculation will not be trustworthy. Assuming
that they agree, then
=SQRT(MMULT(MMULT(v,MINVERSE(MMULT(TRANSPOSE(X),X) )),TRANSPOSE(v)))*S
(where S is the sey estimate from the LINEST output) is the standard error
of the fitted multiple regression at the predictors v. If you are predicting
a future value instead of just the fitted mean, use
=SQRT(1+MMULT(MMULT(v,MINVERSE(MMULT(TRANSPOSE(X), X))),TRANSPOSE(v)))*S
A 1-alpha level confidence interval is then
=prediction +/- TINV(alpha,df)*SE
so that alpha=0.05 would give a 95% confidence interval.

Jerry

"FredZack" wrote:

Is there a way to get a confidence interval about your predicted values for
your dependent variable (Y) in Excels multiple regression?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Regression in Analysis Tool Pack - Confidence Intervals Ashley B New Users to Excel 1 September 4th 09 10:41 AM
Regression and the confidence intervals Denis Excel Worksheet Functions 3 January 3rd 09 02:15 AM
Confidence Intervals waldoafit Excel Discussion (Misc queries) 2 January 10th 07 08:36 PM
Chart confidence intervals around regression (or trend) line LeAnne Charts and Charting in Excel 2 December 1st 05 06:53 PM
need to make chart w/ confidence intervals and don't know how penguinchick Charts and Charting in Excel 0 September 20th 05 06:41 PM


All times are GMT +1. The time now is 03:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"