Excel: Calculate Confidence Interval for Regression Coefficient

In a linear regression model, a regression coefficient tells us the average change in the response variable 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)

Notice that the regression coefficient for hours is 1.982.

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:

confidence interval for regression coefficient in Excel

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

Since this confidence interval doesn’t contain the value 0, 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: