You're working with a multivariable problem and want to compute the correlation coefficient between variables.

Use Excel's built-in functions `CORREL`

or `PEARSON`

, or use the Correlation tool available in the Analysis ToolPak.

In Recipe 8.4, I show you how to perform linear regression where the dependent variable depends on more than one independent variable. The example I discuss in that recipe involves six independent variables. This is a typical example where it's prudent to assess the correlation between independent variables before conducting the regression analysis. This way you can avoid using highly correlated (or colinear) independent variables, which can give you trouble.

Excel provides two built-in functions that allow you to compute the Pearson product-moment correlation coefficients between variables. These functions are `CORREL`

and `PEARSON`

and, according to Excel's help documents, they perform exactly the same calculation. (I've no idea why there are two functions to do the same thing.)

Figure 5-8 shows the data from the multiple linear regression example I discuss in Chapter 8. For now, I'll show you how to examine the correlation between independent variables—the *x* variables—using Excel's correlation functions as well as the Analysis ToolPak.

To compute the correlation coefficient between variables `x1`

and `x2`

, you can use the cell formula `=CORREL(E7:E22,F7:F22)`

or `=PEARSON(E7:E22,F7:F22)`

. They both produce the same results, which ...

Start Free Trial

No credit card required