perspectives on innovative supply chain management strategies
Contact Us:

How to Run a Multivariate Regression in Excel

Multivariate regression enables you to relate one dependent variable to multiple independent variables you’ve derived from surveys or measurements. This type of data analysis helps you search for the effects of your observed data on a related condition or predict a condition based on other related observations. It can reveal predictive patterns that help you understand your customers and enhance the market for your products. Before you invest in an advanced statistical software package, you can perform multivariate regression within the familiar interface of Microsoft Excel.

Step 1
Launch Microsoft Excel and click on the “File” tab in the ribbon. Select “Options” to open the Excel Options dialog box.

Step 2
Click the “Add-Ins” item in the list on the left side of the dialog box. You will see “Analysis ToolPak” in the list of Inactive Application Add-Ins. Set the Manage drop-down menu to “Excel Add-Ins,” and click on the “Go” button at the bottom of the dialog box to open the Add-Ins dialog box.

Step 3
Tick the check box in front of “Analysis ToolPak” in the list of available add-ins. Click the “OK” button on the right side of the Add-Ins dialog box to turn on the Analysis ToolPak once you have selected it in the list of options.

Step 4
Enter your column headings in row 1 of your worksheet, and input your data below the respective headings. You can enter your dependent variable in the first or last column of your data. Use consecutive columns for the data defining your independent variable’s components.

Step 5
Switch to the “Data” tab in the Microsoft Excel ribbon and locate the “Analysis” group. Click on the “Data Analysis” item to bring up the dialog box of the same name.

Step 6
Scroll through the list of Analysis Tools until you locate “Regression.” Click on it to select it, and then click on the “OK” button at the right to open its dialog box.

Step 7
Type the location of the cell range that contains your dependent variable into the “Input Y Range” field in the Input section of the Regression dialog box. You can click on the unlabeled “Collapse Dialog” button at the right edge of the field to reduce the dialog box height temporarily so you can fill in the field by clicking on the heading of the relevant data column. After you identify your data range, click on the “Restore Dialog” button at the right edge of the input field to regain access to the full dialog box.

Step 8
Enter the location of the cell range that contains your independent variable – also called a predictor or explanatory variable – into the “Input X Range” field.

Step 9
Click on the “Labels” check box to tell Excel that the first row of your data contains data labels. If you didn’t enter a header row, leave this box unchecked.

Step 10
Click on the “Output Range” radio button in the Output Options section of the dialog box and enter a data range in the entry field to identify a location in your current worksheet for the output of your analysis. Use the “Collapse Dialog” and “Restore Dialog” buttons to click through your worksheet and identify where to put your results. Select the “New Worksheet Ply” radio button to place your results in a new sheet within your workbook, or choose “New Workbook” to put the results in a new file.

Step 11
Choose options from the Residuals section of the Regression dialog box. Residuals summarize the statistical output of the analysis on a case-by-case basis, comparing the prediction derived from the regression equation to the difference between it and the actual score. Standardized Residuals adjust the standard deviation of Residuals to a value of 1. Tick the check box in front of the Plot options to graph your results. The Residual Plot graphs your residuals, and the Line Fit Plot compares the regression’s prediction to its actual output.

Step 12
Click on the “OK” button at the right of the Regression dialog box to process your regression. View your results in the location you specified, either on your current worksheet, elsewhere in your document or in another file.

•    The “Collapse Dialog” and “Restore Dialog” buttons replace each other on a context-sensitive basis.
•    If you can’t locate the Analysis ToolPak and Excel prompts you to install it, click on the “Yes” button to authorize its installation.
•    You can activate the Analysis ToolPak’s accompanying set of Visual Basic for Applications functions at the same time you activate the ToolPak itself. The VBA functions appear below the ToolPak in the Add-Ins Available list.
•    The data analysis functions in the Analysis ToolPak only operate in one worksheet out of an Excel document. Look on the first of a set of grouped worksheets for your results. You must recalculate your analysis for the rest of the worksheets in the group.

•    You must use at least three variables to perform a multivariate regression.


Be Sociable, Share!