The Linear Regression Functions. Excel also includes linear regression functions that you can find the slope, intercept and r square values with for y and x data arrays. Select a spreadsheet cell to add one of those functions to, and then press the Insert Function button. It is also standard with the 2016 or later Mac version of Excel. However, it is not standard with earlier versions of Excel for Mac. WEEK 1 Module 1: Regression Analysis: An Introduction In this module you will get introduced to the Linear Regression Model. We will build a regression model and estimate it using Excel.
Related Articles
- 1 Do a T-Test on Excel for the Mac
- 2 Make a Frequency Table on Microsoft Excel
- 3 Chart the Frequency of a Data Set on Excel
- 4 Draw a Graph in MS Word
Linear regression models predict the outcome of one variable based on the value of another, correlated variable. Excel 2013 can compare this data to determine the correlation which is defined by a regression equation. This equation calculates the expected value of the second variable based on the actual value of the first variable. However, the actual value probably deviates from this expected value. This difference is called its residual. A residual plot charts these values against the first variable to visually display the effectiveness of the equation.
1.Enter the first variable’s data in column A and the second variable’s data in column B. As an example, you might enter aptitude test scores in cells A1 through A15 and sales totals in cells B1 through B15.
2.Click and drag your mouse across both data sets to highlight all the values. In the example, highlight cells A1 through B15.
3.Click the “Insert” tab, choose “Insert Scatter (X,Y) or Bubble Chart” from the Charts group and select the first “Scatter” option to create a scatter chart.
4.Click “Add Chart Elements” from the Design tab, “Trendline” and then “More Trendline Options.”
5.Leave “Linear” selected and check “Display Equation on Chart.” Close the “Format Trendline” side panel.
6.Enter the equation that appears on the chart in cell C1, but replace “X” with “A1.” As an example, if the displayed equation is “y = 362.46x + 26259, enter “=362.46*A1+26356” without quotes in cell C1. This calculates the predicted value.
7.Click the cell again and double-click the small, black “Fill Handle” at the bottom right of the cell. Doing so copies the data down the rest of the column until it reaches the last data entry.
8.Enter “=B1-C1” without quotes in cell D1 to calculate the residual, or the predicted value’s deviation from the actual amount.
9.Click the cell again and double-click the “Fill Handle” to copy the formula and display the residuals for each data entry. The data in column D should remain highlighted.
10.Hold the “Ctrl” key and highlight the data in column A. In the example, you should have cells A1 through A15 and D1 through D15 highlighted.
11.Click the “Insert” tab, choose “Insert Scatter (X,Y) or Bubble Chart” from the Charts group and select the first “Scatter” option to create a residual plot. If the dots tightly adhere to the zero baseline, the regression equation is reasonably accurate. If the dots are wildly scattered, the regression equation may have limited usefulness.
References (2)
Choose Citation Style
Taylor, C.. 'How to Do Residuals in Excel.' Small Business - Chron.com, http://smallbusiness.chron.com/residuals-excel-74433.html. Accessed 26 December 2019.
Taylor, C.. (n.d.). How to Do Residuals in Excel. Small Business - Chron.com. Retrieved from http://smallbusiness.chron.com/residuals-excel-74433.html
Taylor, C.. 'How to Do Residuals in Excel' accessed December 26, 2019. http://smallbusiness.chron.com/residuals-excel-74433.html
Note: Depending on which text editor you're pasting into, you might have to add the italics to the site name.