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
- α = 1 – 95% = 0.05
- df = degree of freedom
- Syx = Standard error in estimation
- n = sample count
- x̅ = 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.
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.
- After that, the following chart will be created.
- 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.
- Next check the Display Equation on chart checkbox.
- 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.
- 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.
- 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.
- 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.
- 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.
- Now apply the following formula in cell F11 to calculate the t-value. Here we will use the T.INV.2T function for that.
- 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.
- Then apply the following formula in cell D18 to get the y values at the Upper 95% Confidence Interval.
- Finally, enter the following formula in cell E18 to get the y values at the 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.
- Then, click on the Add button as shown below.
- 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.
- Similarly insert the lower 95% confidence intervals in the chart. Next, make sure all the Data Series are checked and click OK.
- After that, the regression chart will look as follows.
- Now 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 of the confidence interval data series and click OK.
- 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.
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.
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.
- How to Find Confidence Interval in Excel for Two Samples
- Calculate Z-Score with 95 Confidence Interval in Excel
- How to Calculate Confidence Interval for Population Mean in Excel
- Excel Confidence Interval for Difference in Means (2 Examples)
- How to Calculate P-Value from Confidence Interval in Excel
- Calculate 90 Percent Confidence Interval in Excel
- How to Find Upper and Lower Limits of Confidence Interval in Excel