In statistical modeling, we often encounter the issue of multi-collinearity phenomena among the explanatory variables. Our objective here is to uncover a strong relationship of collinearity among the explanatory variables and determine whether one or more variables can be linearly predicted from the others with non-trivial accuracy.
NumXL provides an intuitive interface to help Excel users conduct a multi-collinearity test using several methods. In this tutorial, we’ll demonstrate the steps to construct a multi-collinearity test of the hypothesis using NumXL functions in Excel.
- Select an empty cell to store the test table
- Locate the Statistical Test (STAT TEST) icon in the toolbar (or menu in Excel 2003) and click on the "multi-collinearity" test icon in the list down-arrow.
- The Multi-collinearity Test dialog box appears. Select the “Input Data” range for the explanatory variable. Each column represents a separate variable. The “Output” field is set by default to the currently selected cell in your worksheet.
- Selecting the input data cell range will enable the “Options” and “Missing Values” tabs. Click the “Options” Tab.
- Select the multi-collinearity tests to include in the output table.
Note: Initially, all tests are selected as shown above.
- If your data include one or more intermediate observations with missing values, click the “Missing Values” tab.
- Initially, missing values are dropped from the sample data. If you wish to change this behavior/treatment, select an alternative method.
- Now, Click “OK” to generate the output table.
- The normality test wizard generates the output table as shown below:
The multi-collinearity test function and wizard are available starting with version 1.60.