O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

5.4. Correlating Data

Problem

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

Solution

Use Excel's built-in functions CORREL or PEARSON, or use the Correlation tool available in the Analysis ToolPak.

Discussion

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required