Research Skills: Week 86

Regression Analysis using Excel

Learn
Regression analysis can be performed in SPSS software.

Unlearn
Basic software such as Excel would allow you to conduct regression analysis as well. Thus we need not always rely on SPSS software.

Relearn
To do regression in Excel, you need the Analysis Toolpak add-in to be installed in Excel. This was an option when you installed Excel, but you might not have selected it. If you didn't install it, Excel will ask you for the CD, when you try to add the toolpak. Check that the add-in is installed, and added-in, by choosing Add-ins from the tools menu. Ensure that "Analysis ToolPak" is selected. You can now use the data analysis functions in Excel, which include multiple regression. Below is the steps on how to run regression analysis using Excel.

Step 1: To get to the data analysis function in Excel, you select the Tools menu, and then choose Data Analysis.
Step 2: This will lead to another Dialog, click on Regression and then click OK.
Step 3: Next we tell Excel about the data that we would like to analyse. The first box is the input Y range. Here, we tell Excel about our dependent variable. The dependent variable must be a column, 1 cell wide, and N cells long (where N is the number of individuals that we are analysing). You can either type the information in directly as D or you can select the appropriate data from the spreadsheet.
Step 4: If information is included in row 1, which includes the variable name, we are going to have to tell Excel this, by clicking on the "Labels" checkbox.
Step 5: The next step is to input the independent variables. The independent variables must be a block of data, of k columns (where k is the number of independent variables) and N rows (where N is still the number of people). Step 6: Next we tell Excel where we want the results to be written. It is best to ask for a new sheet - you don't want to accidentally overwrite some of your precious data, and have to go to all of the effort of restoring it from a backup.
Step7:So,finally,weclickOK.And we get a lot of output, written to a new sheet. A note about this output - output from analysis in Excel is usually "live" that is to say, the data are linked to the output. If you change the data, you will change the output. This is not the case for this type of output in Excel. The results of the analysis are "dead" and will not change.