Excel 2010 Bible

Chapter 38: Analyzing Data with the Analysis ToolPak

785

expressed as a coefficient that ranges from –1.0 (a perfect negative correlation) to +1.0 (a perfect

positive correlation). A correlation coefficient of 0 indicates that the two variables aren’t correlated.

Figure 38.3 shows the Correlation dialog box. Specify the input range, which can include any

number of variables, arranged in rows or columns.

FIGURE 38.3

The Correlation dialog box.

The output consists of a correlation matrix that shows the correlation coefficient for each variable

paired with every other variable.

Note
The resulting correlation matrix doesn’t use formulas to calculate the results. Therefore, if any data changes,
the correlation matrix isn’t valid. You can use the CORREL function to create a correlation matrix that changes
automatically when you change data. n

Covariance

The Covariance tool produces a matrix that is similar to the one generated by the Correlation tool.

Covariance, like correlation, measures the degree to which two variables vary together. Specifically,

covariance is the average of the product of the deviations of each data point pair from their respec-

tive means.

Because the Covariance tool does not generate formulas, you may prefer to calculate a covariance

matrix using the COVAR function.

Descriptive Statistics

The Descriptive Statistics tool produces a table that describes your data with some standard statis-

tics. Figure 38.4 shows some sample output.