# Linear Regression Confidence Interval in Excel: Calculation and Interpretation

### Steps

• Select the entire dataset (B4:C13). Then go to Insert >> Insert Scatter (X, Y) or Bubble Chart >> Scatter as shown below.

• The following chart will be created.

• Click the chart Element icon in the upper-right corner and enable linear Trendline. Go to More Options or double-click Trendline.

• Check the Display Equation on chart checkbox.

• Y = -0.0532x + 8.704 will appear on the chart. Comparing it to the linear regression equation yields m = -0.0532 and b = 8.704. Use the SLOPE and INTERCEPT functions to calculate these results.

• Enter the values of Slope (m), Intercept (b), and Observations (n) in cells F5:F7 Use the COUNT function to get the sample count or observations to be 9.

• Enter the following formula in cell F8 to calculate the Standard Error. The STEYX function returns the standard error of the predicted y-value for each x in a regression.
`=STEYX(C5:C13,B5:B13)`

• Apply the following formula in cell F9 to get the sample mean or average. Here we will use theÂ AVERAGE function to do that.
`=AVERAGE(B5:B13)`

• Enter the following formula in cell F10 to get the sum of squares of the variances. The DEVSQ function returns the sum of squares of deviations of data points from their sample.
`=DEVSQ(B5:B13)`

• Apply the following formula in cell F11 to calculate the t-value. Use the T.INV.2T function for that.
`=T.INV.2T(0.05,F7-2)`

• Calculate the confidence interval for each data point. The range of x is 0 to 93. Calculate the confidence intervals for the x values 0, 10, 20,…….,100 as shown in cells B18:B28. Enter the formula in cell C18 and drag the Fill Handle icon below to do that. The SQRT function returns the square root of values.
`=\$F\$11*\$F\$8*SQRT(1/\$F\$7+(B18-\$F\$9)^2/\$F\$10)`

• Apply the following formula in cell D18 to get the y values at the Upper 95% Confidence Interval.
`=(\$F\$5*B18+\$F\$6)+C18`

• Enter the following formula in cell E18 to get the y values at the Lower 95% Confidence Interval.
`=(\$F\$5*B18+\$F\$6)-C18`

## Interpreting Confidence Intervals in Linear Regression

• Right-click on the Chart Area to go to Select Data.

• Enter a Series name for the Upper 95 Confidence Intervals. Use the x column for Series X Values and the y + CI column for Series Y Values. Click OK.

• Insert the lower 95% confidence intervals in the chart. Make sure all the Data Series are checked, and click OK.

• The regression chart will look as follows.

• Right-click on any of the confidence interval data series and click on Change Series Chart Type.

• Then change the chart type to Scatter with Smooth Lines for both confidence interval data series and click OK.

• The regression chart will look as follows. The chart explains that the assumption of the 95% probability of the regression data points falling between the confidence intervals is valid.

## Things to Remember

• Donâ€™t forget to apply absolute references in the formulas when necessary to avoid erroneous results.
• A confidence interval is not a guarantee but rather a probability.

## Related Articles

<< Go Back to Confidence Interval Excel | Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF