Show Excel Regression Analysis (Table of Contents) Regression Analysis in ExcelLinear regression is a statistical technique that examines the linear relationship between a dependent variable and one or more independent variables.
Linear relationship means the change in an independent variable(s) causes a change in the dependent variable. There are basically two types of linear relationships as well.
These were some of the pre-requisites before you actually proceed towards regression analysis in excel. There are two basic ways to perform linear regression in excel using:
There is actually one more method which is using manual formula’s to calculate linear regression. But why should you go for it when excel does calculations for you? Therefore, we are going to talk about the two methods discussed above only. Suppose you have data on the height and weight of 10 individuals. If you plot this information through a chart, let’s see what it gives. As the above screenshot shows, the linear relationship can be found in Height and Weight through the graph. Don’t get much involved in graphs now; we are anyhow going to dig it deep in the second portion of this article. Explanation of Regression MathematicallyWe have a mathematical expression for linear regression as below: Y = aX + b + ε Where,
In that case, the equation becomes, Y = aX + b Which can be represented as: Weight = a*Height + b We’ll try to find out the values of these a and b using methods we have discussed above. How to Perform Linear Regression in Excel?The further article explains the basics of regression analysis in excel and shows a few different ways to do linear regression in Excel. #1 – Regression Tool Using Analysis ToolPak in ExcelFor our example, we’ll try to fit regression for Weight values (which is a dependent variable) with the help of Height values (which is an independent variable).
Till here, it was easy and not that logical. However, interpreting this output and make valuable insights from it is a tricky task. One important part of this entire output is R Square/ Adjusted R Square under the SUMMARY OUTPUT table, which provides information, how good our model is fit. In this case, the R Square value is 0.9547, which interprets that the model has a 95.47% accuracy (good fit). Or in another language, information about the Y variable is explained 95.47% by the X variable. The other important part of the entire output is a table of coefficients. It gives values of coefficients that can be used to build the model for future predictions. Now our, regression equation for prediction becomes: Weight = 0.6746*Height – 38.45508 (Slope value for Height is 0.6746… and Intercept is -38.45508…) Did you get what you have defined? You have defined a function in which you now just have to put the value of Height, and you’ll get the Weight value. #2 – Regression Analysis Using Scatterplot with Trendline in ExcelNow, we’ll see how in excel, we can fit a regression equation on a scatterplot itself.
This is the equation using which we can predict the weight values for any given set of Height values. Things to Remember About Regression Analysis in Excel
Recommended ArticlesThis has been a guide to Regression Analysis in Excel. Here we discuss how to do Regression Analysis in Excel along with excel examples and a downloadable excel template. You can also go through our other suggested articles – |