How can I use excel for linear regression?
Answers
Answer:
Explanation:
1) Using the Tools menu version of the regression analysis to obtain the results of the analysis in a table.
In order to do this version of the linear regression analysis, using Excel, you have to begin by creating a data table that has the independent and dependent variables. This table has to have the data in columns, not rows, in order for the regression to work properly. A sample data table is shown below. (If you have created a table in rows, not columns, it is easy to transform it into a columnar table. Copy the table and then do a Paste Special to a new location. In the Paste Special menu, select Transpose (and Paste Values if the table is made by cells with formulas) and the new table that will be created will convert the rows into columns. A table created this way is shown below.
To do the linear regression, go to the Tools Menu and select Data Analysis. From the Data Analysis window select Regression. That will open a wizard that will look like the picture below:
The next step is to tell the Regression Wizard the things it needs to know; the location of the Y data, the location of the X data, and the place to put the result of the regression analysis. In the example shown the Y range would be the column of RTs beginning with 667 and ending with 1210. The X range would be the column beginning with 0 degrees and ending with 180 degrees. Each of these can be filled in by putting the cursor in the window you want to fill in and then clicking on the top of the column and dragging to the bottom, holding the left button down. That will copy the cell references into the blank. Move the cursor into the next blank to be filled and repeat. In the Output Options section, you have the opportunity either to have the result of the regression analysis put on a new, blank page in your Excel workbook, or to be located on the same page as the data. To locate the result on the same page as the data, click in the button labeled Output range, and then click in the box to the right of that button to move your cursor there. Now, click on a cell that you want to be the upper left hand corner of the output and that cell location will be put into the wizard. Click the OK button and the result of the regression analysis will be located in the spot that you have chosen.
Highlighted in the sample output shown above are the value of R Square (.81), the Y Intercept value (574 msec) and the slope, which is identified as the X Variable 1 (2.88 msec/degree). These are the only parts of the output that will be relevant to the things that we will do in this course.
2) Doing a linear regression analysis where the results will be displayed as a line on a graph with the option of also including the equation for the line and/or the value of R squared.
To begin, create a graph of the data. Because linear regression implies that both the X and Y variables have at least interval or ratio properties, the appropriate chart type to select from the menu when creating the chart is an XY Scatterplot. A sample of a graph of the data shown above, created this way, appears below.
Now, select the chart so that the "handles" are visible, the small squares at each of the corners and the midpoints of each of the lines that form the box around the graph. That will cause the Chart menu to become available at the top of the page. From the Chart menu select Add Trendline (because a linear regression is one kind of curve...trendline...that Excel can fit to a set of data). That will produce a menu that looks like the one shown below.
In the menu the box labeled Linear has been selected. That will cause a straight line to be fit to the data. If you click on the Options tab in the top of the window, you will have another set of choices, which are shown in the next picture.
I have checked the boxes to Display equation on chart and to Display r-squared value on chart. If you want neither of these, or only one, to appear on the chart, click on the boxes to add or remove check marks as desired. Below is a picture of the graph that results when both boxes have been checked.
Note that the line has been drawn, it has been added to the Legend, and the equation and R squared have been printed on the graph. The equation and R squared can be dragged to other locations in the graph, their fonts formatted differently, etc. Working with them is just like working with other objects in the chart. Note that the values for the slope, Y intercept, and R squared are the same as were obtained using the first form of the analysis.