Multi-collinearity test in Excel

Scene 1:

Hello and welcome to a NumXL 1.6 tutorial. In this video I'll demonstrate the steps for carrying out a multicollinearity test.

For our sample data I've generated a series of six random variables.

First select a cell in your worksheet where you want the analysis output to be located. Next locate the statistical test icon in the NumXL tab and from the drop down menu click on multicollinearity tests.

Scene 2:

In the multicollinearity wizard select the cells range for your input data. Note that each column represents a separate variable. The output field is set by default to the selected cell in your worksheet. Let's first select the input data.

Once you've selected a valid cells range for the input, the options and missing value steps become available. Let's click on the options tab.

Scene 3:

In front of us is a list of the supported methods for collinearity tests. By default both variance inflation factor and condition number methods are checked.

Scene 4:

In the missing values tab we'll leave the default selection which removes any observation with a missing value in any of the variables. You may now click on the OK button.

Scene 5:

The output table is generated. Note that the condition number applies to the whole input.

A condition number that's around 30 or more indicates the presence of multicollinearity. Now we have a small number which means there is no multicollinearity.

For the variance inflation factor or VIF, multicollinearity isn't present if the number is greater than 5.

Scene 6:

That is it for now, thank you for watching!