How to Get Regression Statistics in Excel (3 Easy Ways)

Sometimes, we need to apply regression statistics in our dataset to know which factors impact the dataset. We can also find the relation between two variables using regression statistics. Today, we will demonstrate 3 easy methods to get regression statistics in Excel. So, without further ado, let’s start the discussion.


Download Practice Book

Download the practice book here.


What Is Regression Statistics?

Regression statistics is a method that defines relationships between two or more variables. There are two types of variables in regression statistics.

Dependent Variable: It is the main variable you need to predict with regression analysis.

Independent Variable: It influences the dependent variable. There can be one or more independent variables.

There are many types of regression models. But today we will use the linear regression model to explain the process. You can also find other types of regression models after reading the whole article.

The linear regression equation is given below:

y = bx + a + e

Here:

  • y is the dependent variable.
  • x is the independent variable.
  • b is the slope of a regression model and it indicates the rate of change for y as changes.
  • a is the Y-intercept. It is the point where the line crosses the Y-axis.
  • e indicates the amount of error.

In Excel, we use the regression without the error. This is called the least-squares regression. So, the equation becomes:

y = bx + a

In the following section, we will demonstrate how we can get the regression statistics easily in Excel.


3 Ways to Get Regression Statistics in Excel

To explain the methods, we will use a dataset that contains information about the height (in cm) and weight (in kg) of some employees. Here, we will use the dataset and create a relationship between height and weight. We want to know how the weight changes if the height changes. So, in this case, weight is the dependent variable and height is the independent variable. Throughout the article, we will use the same dataset.


1. Enable Analysis ToolPak to Get Regression Statistics in Excel

In the first method, we will enable the Analysis ToolPak to get regression statistics in Excel. This process is very easy and efficient. To know more about the technique, pay attention to the steps below:

STEPS:

  • First of all, click on the File tab.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • Secondly, select Options. It will open the Excel Options window.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • In the Excel Options window, select Add-ins >> Excel Add-ins >> Go.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • After that, the Add-ins message box will pop up.
  • Check Analysis ToolPak and click OK to proceed.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • In the following step, go to the Data tab and select Data Analysis.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • Next, select Regression from the Data Analysis message box and click OK. It will open the Regression dialog box.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • In the Regression dialog box, select the Input Y Range and then, select Input X Range.
  • Then, select Labels, New Worksheet Ply, and Residuals.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • In the end, click OK to get Regression Statistics in a new sheet like the picture below. Here, the first table indicates the Regression Statistics.

Enable Analysis ToolPak to Get Regression Statistics in Excel

  • You will also see the RESIDUAL OUTPUT in the same sheet.

Enable Analysis ToolPak to Get Regression Statistics in Excel

Note: If you want to plot the dataset to show the graph of regression statistics, then please learn Method-3.

Read More: Multiple Regression Analysis with Excel


2. Insert Formulas to Get Regression Statistics in Excel

We can also use some formulas to get regression statistics. Here, you will see an array formula. Also, we will show how to compute it without an array formula. To get the results, we will use the LINEST, INTERCEPT, SLOPE, and CORREL functions. Here, we will use the same dataset but the structure will be slightly different. Here, a is the Y-intercept and b is the slope of the regression line.

Insert Formulas to Get Regression Statistics in Excel

Let’s follow the steps below to learn more.

STEPS:

  • In the first place, select Cell F4 & G4.

Insert Formulas to Get Regression Statistics in Excel

  • Secondly, type the formula in the Formula Bar:
=LINEST(D5:D11,C5:C11)

Insert Formulas to Get Regression Statistics in Excel

  • After that, press Ctrl + Shift + Enter to see the result.

Insert Formulas to Get Regression Statistics in Excel

  • Alternatively, you can avoid the array formula.
  • To do so, select Cell F7 and type the formula:
=INTERCEPT(D5:D11,C5:C11)
  • Hit Enter to see the result.

Insert Formulas to Get Regression Statistics in Excel

  • Now, to find the slope, type the formula in Cell F10:
=SLOPE(D5:D11,C5:C11)
  • And press Enter.

Insert Formulas to Get Regression Statistics in Excel

  • Finally, to see the correlation coefficient, type the formula in Cell F13:
=CORREL(C5:C11,D5:D11)
  • And press Enter.

Insert Formulas to Get Regression Statistics in Excel

Read More: Multiple Linear Regression on Excel Data Sets (2 Methods)


Similar Readings


3. Get Regression Statistics with Scatter Plot in Excel

We can also get the regression statistics with a scatter plot in Excel. Here, we will plot the variables in a graph and see the trend of change or dependence. So, without any delay, let’s observe the steps below to know more.

STEPS:

  • In the beginning, select the data with headers.

  • In the second step, go to the Insert tab and select the Insert Scatter icon. A drop-down menu will occur.
  • Select the first icon from the drop-down menu. It indicates the scatter plot.

  • After that, you will see a graph on your worksheet.

  • Now, click on the plus (+) sign to add Axis Titles.

  • Next, change the title and axis titles to make the graph more meaningful.

  • In the following step, click on the plus (+) sign again and select Trendline.

  • After selecting Trendline, you will see a line in the graph like the picture below.

  • In the end, if you click on the Trendline, the Trendline Options will appear on the left side of the screen. You can choose different types of regression from here.

Get Regression Statistics with Scatter Plot in Excel

Read More: How to Do Simple Linear Regression in Excel (4 Simple Methods)


Things to Remember

There are certain things you need to remember when you are trying to get regression statistics in Excel.

  • In Method-1 & Method-3, select the values of the X-axis and Y-axis.
  • In Method-2, use Ctrl + Shift + Enter to use the array formula perfectly.

Conclusion

In this article, we have demonstrated 3 easy methods to Get Regression Statistics in Excel. Here, we have used practical datasets to explain the process. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo