When the Excel Options window opens, click on Add-Ins located on the left pane. At the bottom of the window select Excel Add-ins from the drop-down to the right of Manage:, click Go to proceed. The Add-Ins window will open, add a checkmark to the check box next to Analysis ToolPak, click OK.
Psychology 403 Laboratory in Cognitive Psychology Doing a Linear Regression Analysis, Using Excel (versionfrom Office '97)
There are actually two ways to do a linear regression analysis usingExcel. The first is done using the Tools menu, and resultsin a tabular output that contains the relevant information. The secondis done if data have been graphed and you wish to plot the regression lineon the graph. In this version you have the choice of also havingthe equation for the line and/or the value of R squared included on thegraph.
1) Using the Tools menu version of the regression analysisto obtain the results of the analysis in a table.
In order to do this version of the linear regression analysis, usingExcel, you have to begin by creating a data table that has the independentand dependent variables. This table has to have the data in columns,not rows, in order for the regression to work properly. A sampledata table is shown below. (If you have created a table in rows,not columns, it is easy to transform it into a columnar table. Copythe table and then do a Paste Special to a new location. Inthe Paste Special menu, select Transpose (and Paste Valuesifthe table is made by cells with formulas) and the new table that will becreated will convert the rows into columns. A table created thisway is shown below.
To do the linear regression, go to the Tools Menu and selectDataAnalysis. From the Data Analysis window selectRegression. 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 toknow; the location of the Y data, the location of the X data, andthe place to put the result of the regression analysis. In the exampleshown the Y range would be the column of RTs beginning with 667 and endingwith 1210. The X range would be the column beginning with 0 degreesand ending with 180 degrees. Each of these can be filled in by puttingthe cursor in the window you want to fill in and then clicking on the topof the column and dragging to the bottom, holding the left button down. That will copy the cell references into the blank. Move the cursorinto the next blank to be filled and repeat. In the Output Optionssection, you have the opportunity either to have the result of the regressionanalysis put on a new, blank page in your Excel workbook, or to be locatedon the same page as the data. To locate the result on the same pageas the data, click in the button labeled Output range, and thenclick 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 ofthe output and that cell location will be put into the wizard. Clickthe OK button and the result of the regression analysis will be locatedin the spot that you have chosen.
How To Use The Data Analysis Tool In Excel
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 identifiedas the X Variable 1 (2.88 msec/degree). These are the only partsof the output that will be relevant to the things that we will do in thiscourse.
2) Doing a linear regression analysis where the results willbe displayed as a line on a graph with the option of also including theequation for the line and/or the value of R squared.
Best monitor color calibration tool. To reach calibration, scroll to the bottom of the page and select the option labeled “Advanced Display Settings.” 3.
How To Get The Data Analysis Tool On Excel 2011 For Mac
To begin, create a graph of the data. Because linear regressionimplies that both the X and Y variables have at least interval or ratioproperties, the appropriate chart type to select from the menu when creatingthe chart is an XY Scatterplot. A sample of a graph of thedata shown above, created this way, appears below.
Now, select the chart so that the 'handles' are visible, the small squaresat each of the corners and the midpoints of each of the lines that formthe box around the graph. That will cause the Chart menu tobecome available at the top of the page. From the Chart menuselect Add Trendline (because a linear regression is one kind of curve..trendline..thatExcel can fit to a set of data). That will produce a menu that lookslike the one shown below.
In the menu the box labeled Linear has been selected. That willcause a straight line to be fit to the data. If you click on theOptionstab in the top of the window, you will have another set of choices, whichare shown in the next picture.
How To Get The Data Analysis Tool On Excel 2011 For Mac Download
I have checked the boxes to Display equation on chart and toDisplayr-squared value on chart. If you want neither of these, or onlyone, to appear on the chart, click on the boxes to add or remove checkmarks as desired. Below is a picture of the graph that results whenboth boxes have been checked.
Where Is The Data Analysis Tool
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. Theequation and R squared can be dragged to other locations in the graph,their fonts formatted differently, etc. Working with them is justlike working with other objects in the chart. Note that the valuesfor the slope, Y intercept, and R squared are the same as were obtainedusing the first form of the analysis.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |