http://office.microsoft.com/en-us/marketplace/EM103681821033.aspx
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
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)),MMULT(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
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