From Definition 3 and Property 1 of Method of Least Squares for Multiple Regression, recall that

If *X ^{T}X* is singular, i.e. doesn’t have an inverse (see Matrix Operations), then

*B*won’t be defined, and as a result

*Y*-hat will not be defined. This occurs when one column in

*X*is a non-trivial linear combination of some other columns in

*X*, i.e. one independent variable is a non-trivial linear combination of the other independent variables. Even when

*X*is almost singular, i.e. det(

^{T}X*X*) is close to zero, the values of

^{T}X*B*and

*Y*-hat will be unstable; i.e. small changes in

*X*may result in significant changes in

*B*and

*Y*-hat. Such a situation is called

**multicollinearity**, or simply

**collinearity**, and should be avoided.

E.g., in the following table, X1 is double X2. Thus *X ^{T}X* is singular. Excel detects this and creates a regression model equivalent to that obtained by simply eliminating column X2.

**Figure 1 – Collinearity**

**Observation**: In the case where *k* = 2, the coefficient estimates produced by the least square process turn out to be

If *x _{1}* and

*x*are correlated, i.e.

_{2}then the denominators of the coefficients are zero and so the coefficients are undefined.

**Observation**: Unfortunately, you can’t always count on one column being an exact linear combination of the others. Even when one column is almost a linear combination of the other columns, an unstable situation can result. We now define some metrics that help determine whether such a situation is likely.

**Definition 1**: **Tolerance** is 1 – , where

i.e. the multiple coefficient between *x _{j}* and all the other independent variables. The

**variance inflation factor**(

**VIF**) is the reciprocal of the

**tolerance**.

**Observation**: Tolerance ranges from 0 to 1. We want a low value of VIF and a high value of tolerance. A tolerance value of less than 0.1 is a red alert, while values below 0.2 can be cause for concern.

**Real Statistics Excel Functions**: The Real Statistics Resource contains the following two functions:

**TOLERANCE**(R1, *j*) = Tolerance of the *j*th variable for the data in range R1; i.e. 1 –

**VIF**(R1, *j*) = VIF of the* j*th variable for the data in range R1

**Observation**: TOLERANCE(R1, *j*) = 1–RSquare(R1, *j*)

**Example 1**: Check the Tolerance and VIF for the data displayed in Figure 1 of Multiple Correlation (i.e. the data for the first 12 states in Example 1 of Multiple Correlation).

The top part of Figure 2 shows the data for the first 12 states in Example 1. From these data we can calculate the Tolerance and VIF for each of the 8 independent variables.

**Figure 2 – Tolerance and VIF**

For example, to calculate the Tolerance for Crime we need to run the Regression data analysis tool for the data in the range C4:J15 excluding the E column as the Input X vs. the data in the E column as Input Y. (To do this we first need to copy the data so that Input X consists of contiguous cells). We then see that Multiple R Square = .518, and so Tolerance = 1 – .518 = .482 and VIF = 1/.482 = 2.07.

Alternatively we can use the supplemental functions TOLERANCE(C4:J15,3) = .482 and VIF(C4:J15,3) = 2.07 (since Crime is the 3^{rd} variable). The results are shown in the bottom part of Figure 1. Note that we should be concerned about the Traffic Deaths and University variables since their tolerance values are about .1.