In a linear regression model, a tells us the average change in the associated with a one unit increase in the predictor variable.

We can use the following formula to calculate a confidence interval for a regression coefficient:

Confidence Interval for β₁: b₁ ± t_{1-α/2, n-2} * se(b₁)

where:

•  b₁ = Regression coefficient shown in the regression table
• t_{1-∝/2, n-2} = The t critical value for confidence level 1-∝ with n-2 degrees of freedom where n is the total number of observations in our dataset
• se(b₁) = The standard error of b₁ shown in the regression table

The following example shows how to calculate a confidence interval for a regression coefficient in Excel.

Example: Confidence Interval for Regression Coefficient in Excel

Suppose we’d like to fit a simple linear regression model using hours studied as a predictor variable and exam score as a response variable for 15 students in a particular class:

We can type the following formula into cell D2 to perform simple linear regression using the values in the Hours column as the predictor variable and the values in the Score column as the response variable:

=LINEST(B2:B16, A2:A16, TRUE, TRUE)

Note that the first TRUE argument tells Excel to calculate the intercept of the regression equation normally without forcing it to be zero.

The second TRUE argument tells Excel to produce additional regression statistics besides just the coefficients.

The following screenshot shows the output from this formula (we explain what each value in the output represents in the red text below the output):

Using the regression coefficients, we can write the fitted regression equation as:

Score = 65.334 + 1.982*(Hours Studied)

This tells us that each additional one hour increase in studying is associated with an average increase of 1.982 in exam score.

To calculate a 95% confidence interval for the regression coefficient, we can type the following formulas into cells H2 and H3:

• H2: =D2 – T.INV.2T(0.05, E5)*D3
• H3: =D2 + T.INV.2T(0.05, E5)*D3

The following screenshot shows how to use these formulas in practice:

The 95% confidence interval for the regression coefficient is [1.446, 2.518].

Since this confidence interval , we can conclude that there is a statistically significant association between hours studied and exam score.

We can also confirm this is correct by calculating the 95% confidence interval for the regression coefficient by hand:

• 95% C.I. for β₁: b₁ ± t_{1-α/2, n-2} * se(b₁)
• 95% C.I. for β₁: 1.982 ± t_{.975, 15-2} * .248
• 95% C.I. for β₁: 1.982 ± 2.1604 * .248
• 95% C.I. for β₁: [1.446, 2.518]

The 95% confidence interval for the regression coefficient is [1.446, 2.518].

The following tutorials explain how to perform other common tasks in Excel: