Leading the way in Microsoft Office Development

What is a Correlation ?

Correlation is basically whether or not there is any relationship between two sets of data.

If there is any kind of relationship then a change in one variable can be associated with a change in the other.

A lot of relationships are infact linear but very few are actually perfect because there are normally other factors involved

The Correlation component of the Analysis ToolPak will only tell you if there is a correlation.

It will not tell you to what extent the two data sets are related. For this you need to use the Covariance component.

Using Correlation

We are going to check to see if there is a correlation between the number of business cards we give out and the number of people who visit our website.

Select (Tools > Data Analysis)

Input Range - Enter the cell reference for the range of data you want to analyze. The reference must consist of two or more adjacent ranges of data arranged in columns or rows.

Grouped By - To indicate whether the data in the input range is arranged in rows or in columns, click Rows or Columns.

Labels in First Row/Labels in First Column - If the first row of your input range contains labels, select the Labels in First Row check box. If the labels are in the first column of your input range, select the Labels in First Column check box. This check box is clear if your input range has no labels; Microsoft Excel generates appropriate data labels for the output table.

Output Range - Enter the reference for the upper-left cell of the output table. Excel populates only half of the table because correlation between two ranges of data is independent of the order in which the ranges are processed. Cells in the output table with matching row and column coordinates contain the value 1 because each data set correlates exactly with itself.

New Worksheet Ply - Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook - Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Example - Positive Correlation (Very Strong = 1)

Example - Negative Correlation (Very Strong = -1)

The population correlation calculation returns the covariance of two data sets divided by the product of their standard deviations based on the following formulas.

You can use the correlation analysis tool to determine whether two ranges of data move together — that is, whether large values of one set are associated with large values of the other (positive correlation), whether small values of one set are associated with large values of the other (negative correlation), or whether values in both sets are unrelated (correlation near zero).