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.