Using Excel to Calculate Confidence Intervals for y
Recall that if we were calculating a confidence interval for
the population mean, m, the confidence
interval would be
is the value that you
looked up in the t-table with confidence level a
and n = n - 1 degrees of
freedom. 
is called the
standard error.
Confidence intervals for y in regression problems are calculated with the formula
where 
is the predicted value of y at x = 28 (this is from Part B), 
is the value from the
t-table with confidence level a
and n = n - 2 degrees of freedom,
and
is the standard error for y.
The standard error is in the Regression Statistics table (the first table) that Excel generates when you do a regression analysis. If you look at the output for Example 2 that I did in class (mileage and book values of used cars), the Excel output is
|
SUMMARY
OUTPUT |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Regression Statistics |
|
|
|
|
|
|
|
|
|
Multiple
R |
0.893417642 |
|
|
|
|
|
|
|
|
R Square |
0.798195083 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.772969468 |
|
|
|
|
|
|
|
|
Standard
Error |
2.178287887 |
|
|
|
|
|
|
|
|
Observations |
10 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
1 |
150.140495 |
150.140495 |
31.64224512 |
0.000495522 |
|
|
|
|
Residual |
8 |
37.95950496 |
4.744938119 |
|
|
|
|
|
|
Total |
9 |
188.1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
56.20465646 |
3.53516565 |
15.89873348 |
2.45239E-07 |
48.05254458 |
64.35676833 |
48.05254458 |
64.35676833 |
|
Mileage |
-0.266821566 |
0.047433731 |
-5.625144009 |
0.000495522 |
-0.376204015 |
-0.157439116 |
-0.376204015 |
-0.157439116 |
The standard error is 
= 2.178287887.