Linear Regression Confidence Interval in Excel (Calculation and Interpretation)

Get FREE Advanced Excel Exercises with Solutions!

Linear Regression is a prevalent statistical method for regression analysis. Statisticians use it to create a linear relationship between a dependent and an independent variable to be able to predict unknown outcomes. The Confidence Interval is a crucial parameter while performing linear regression analysis in Excel. This article will highlight how to calculate the confidence interval and interpret it using Excel Charts.


Download Practice Workbook

You can download the practice workbook from the download button below.


What Is Linear Regression & Confidence Interval?

Linear Regression is a data analysis method to create a linear relationship between a dependent and an independent variable. The equation for linear regression is y = mx + b where m and b are the slope and intercept respectively. Confidence Interval (CI) is a statistical term referring to a range of values that include a population with an assumed confidence level. It is often used to analyze the statistical significance of a certain estimation. It depends on the sample size, sample variance, and confidence level. Statisticians normally use a 95% confidence level to calculate the confidence interval. The equation for the confidence interval is

± t(α, df) × Syx × √(1/n + (x – x̅)2 / SSx )

where,

  • α = 1 – 95% = 0.05
  • df = degree of freedom
  • Syx = Standard error in estimation
  • n = sample count
  • = sample mean
  • SSx = sum of squares of deviations of datapoints from sample mean

Performing a linear regression analysis with a 95% confidence level suggests that there is a 95% probability that the predicted outcomes may fall within the confidence interval.


How to Calculate Linear Regression Confidence Interval in Excel

Suppose you have the following example dataset where the y variable is dependent on x. Now you want to calculate the linear regression confidence interval for this dataset.

dataset for confidence interval

There are several ways to perform Linear Regression in Excel. But in this article, we will use Scatter Charts so that it is easier for you to understand. Follow the steps below for that.

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

insert Scatter Chart for Linear Regression line

  • After that, the following chart will be created.

scatter chart

  • Now click on the chart Element icon in the upper-right corner and enable linear Trendline. Then go to More Options or double-click on the Trendline.

insert linear trendline

  • Next check the Display Equation on chart checkbox.

format trendline to show linear regression eaquation

  • After that, 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. You can also use the SLOPE and INTERCEPT functions respectively to calculate these results.

linear regression equation

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

slope, intercept and sample count

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

standard error in linear regression

  • Next, 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)

linear regression sample mean

  • After that, enter the following formula in cell F10 to get the sum of squares of the variances. Here the DEVSQ function returns the sum of squares of deviations of data points from their sample mean.
=DEVSQ(B5:B13)

linear regression SSx

  • Now apply the following formula in cell F11 to calculate the t-value. Here we will use the T.INV.2T function for that.
=T.INV.2T(0.05,F7-2)

linear regression t-value

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

linear regression confidence interval

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

linear regression upper 95% confidence interval

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

linear regression lower 95% confidence interval

Read More: How to Calculate Confidence Interval Without Standard Deviation in Excel


Interpreting Confidence Intervals in Linear Regression

Here the Upper 95% and the Lower 95% Confidence Intervals are 9.16 and 8.25 respectively. So, we can be 95% confident that y values from any sample size will fall within this range.

Now we will plot the y values for the 95% confidence intervals to interpret them graphically. Follow the steps below to do that.

  • First, right-click on the Chart Area to go to Select Data.

select data

  • Then, click on the Add button as shown below.

add data series

  • Next, 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. Then click OK.

insert linear regression upper 95% confidence intervals

  • Similarly insert the lower 95% confidence intervals in the chart. Next, make sure all the Data Series are checked and click OK.

linear regression upper and lower 95% confidence intervals

  • After that, the regression chart will look as follows.

linear regression scatter chart with confidence interval

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

change series chart type

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

smooth line chart for confidence intervals

  • Finally, the regression chart will look as follows. You can easily understand from the chart that the assumption of the 95% probability of the regression data points falling between the confidence intervals is quite true.

final linear regression chart with confidence intervals

Read More: How to Make a Confidence Interval Graph in Excel (3 Methods)


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.

Conclusion

Now you know how to calculate the linear regression confidence interval in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo