Linear regressions help you identify the strength of relationships of at least two variables. But rather than doing these calculations manually, you can compute them in sections. Let’s perform a regression analysis in Microsoft Excel.
How to Perform Regression in Microsoft Excel
Consider these two sets of data: on the left are test scores, on the right are hours spent studying for the test. We want to determine the relationship between scores and study.
On the Data tab on Excel’s ribbon, click Data Analysis. You might have to enable this on Excel’s Add-Ins menu. From the list of Analysis Tools, click Regression, then OK.
On the Regression window, Excel asks for several inputs. Input Y Range is the data in Column A. This is your dependent variable. Click and drag inside the box, selecting your Column A data.
Input X Range is your Column B data (also known as your independent variable).
Check the Labels box, then choose an empty cell in your worksheet in the Output Range box. (Hint: Excel will need several empty cells, so ensure you’re choosing an empty section of your worksheet).
Click OK. Excel will return a Summary Report with several statistical values listed. The one of primary interest for regression is R Square. The closer it is to 1, the stronger the relationship. Here, R Square equals 0.961379291.
This symbolizes the geometric regression line; in this case, that approximately 96.14% of scores are explained by study hours. If R Square was very low, it would indicate a less meaningful relationship between the two variables.
How to Identify a Regression Line Formula in Excel
The Summary Report has also provided your regression line formula. You’ll find the numerical inputs listed in the Coefficients column.
regression-formula-in-excel.jpg
Thus, your regression formula here is:
Test Score=49.392 + 5.017 * Study Hours
Thus, if you study for 3 hours, and perform the formula as follows:
Test Score = 49.392 + 5.017 * 3
Your expected test score, according to the regression equation, is 64.443.
As you can see, Excel makes regression analysis easy, thanks to powerful data tools.
T-Test in Excel