How To Calculate Correlation Coefficient Between Two Data Sets

The correlation coefficient is a statistical calculation that is used to examine the relationship between two sets of data. The value of the correlation coefficient tells us about the strength and the nature of the relationship. Correlation coefficient values can range between +1.00 to -1.00. If the value is exactly +1.00, it means that there is a "perfect" positive relationship between two numbers, while a value of exactly -1.00 indicates a "perfect" negative relationship. Most correlation coefficient values lie somewhere between these two values.

There are several different ways to calculate the correlation coefficient, but one of the simplest ways is with Excel.

Step 1

Open Excel 2007 and sum in one column the numbers for the first set of data. For example, you would add the numbers 10, 20, 30, 40, 50 and 60 in the A2, A3, A4, A5, A6 and A7 cells of your Excel worksheet. In a second column, sum the numbers for the second set of data. For example, you would add the numbers 5, 2, 6, 6, 7 and 4 in the B2, B3, B4, B5, B6 and B7 cells of your Excel worksheet. Your goal is to find the correlation coefficient for these two sets of data.

Step 2

Click on the "A9" cell. This is the cell where you will calculate the correlation coefficient.

Step 3

Click on the "Formulas" tab and choose "Insert Function" (this is found on the top left hand side of Excel spreadsheet). The "Insert Function" window will open. Click on the drop-down menu of "Or select a category" and choose "Statistical." Scroll down the "Select a function" window. Choose "CORREL."

Step 4

Click "OK." The "Function Arguments" window will open, and you will see two cells: "Array1" and "Array2." For Array1, enter A2:A7 for first set of data and for Array2, enter B2:B7 for the second set of data. Click "OK."

Step 5

Read your result. In this example, the calculated value of the correlation coefficient is 0.298807.