Linear Regression Excel

Linear Regression in Excel

Linear Regression Excel: There are two ways by which Linear Regression can be done in Excel. First is by using the TOOLS menu. The results from this will be tabular output containing relevant information.

Another way it can be done is by plotting a regression line on a graph once data has been graphed.

At this point you have the choice of having an equation for the line or the r-squared value included on the graph. Also in Linear Regression Excel, you can choose equation for the line and the value of r-squared included on the graph.

Linear Regression Excel using the Tools menu

For this type of Linear Regression Excel, you must begin by creating a data table. Your data table should contain independent as well as dependent variables. Your table must have data in columns and not in rows. When your data is in columns, the regression will work properly.

Also another advantage of data in columns is that it can be easily transformed into a columnar table. Once the table is done, copy it and then do Paste Special to a new location. From the Paste Special menu, choose Transpose then Paste Values in cases where the table is made by cells with formulas.

Doing this helps in converting rows into columns by the newly created table. To perform linear regression, go to Tools menu and choose Data Analysis. After selecting the Data Analysis window, select Regression.

After setting up the Linear Regression Excel Wizard, you need to tell it things it needs to know. You also need to tell it the location of the Y data and the location of the X data.

After that, tell the Regression Wizard where to put results of the regression analysis. In the Output Option, you can either select your results to be put on a new blank page or on the same page as the data of your Excel workbook.

In order to put your results on the same page as the data, select the Output Range button, and click in the box to its right to move your cursor there.

Then click on the cell you prefer to be the upper left hand corner of your output. The cell location will be put into the wizard. After that click the OK button. The result of the regression analysis will be put in the spot you have chosen.

Linear Regression Excel line on graphed data with equation and/ or r-squared value

This is done by first creating a graph of data. The Linear Regression Excel requires that the X and Y variables have ratio or interval properties.

Therefore select an XY Scatterplot chart from the menu. Once that is done, select the chart ensuring handles are visible. And also the small squares on each of the corners and midpoints of every line forming the box around the graph is visible. This helps in making the Chart menu appear at the top of the page.

From this Chart menu, choose Add Trendline. Once the box labeled Linear is selected, a straight line will fit to the data. If you click on the Options tab at the top window, another set of choices will appear.