How to Calculate Correlation Coefficient Between Two Data Sets

How to Calculate Correlation Coefficient Between Two Data Sets
••• anyaberkut/iStock/GettyImages

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.

    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.

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

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

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

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

    Tips

    • Calculate twice to ensure that you have calculated the value of the correlation coefficient correctly.

    Warnings

    • Please note: The navigation within Excel will be slightly different for Excel 2003, Excel for Mac, and other versions of Excel. Click on the "Help" menu within Excel and enter the words "correlation coefficient" if you encounter any problems.

Related Articles

How to Create Matrices on a TI-89
How to Calculate Pearson's R (Pearson Correlations)...
How to Express "Divisible By" in Excel
How to Graph a Distribution for a T-Test
How to Find Correlation Coefficient & Coefficient of...
How to Draw an Array in Math
Subtracting Minutes From Time in Excel
How to Draw a Normal Distribution in Excel
How to Find Zeros of Functions in Excel
How to Calculate Regression Coefficient
How to Subtract Matrices on Excel
How to Graph and Find the Solution on a Calculator
How to Convert CM to Inches in Excel
How to Solve a Parabola
How to Make a Coordinate Plane in MS Excel
How to Find the X Intercept of a Function
How to Know if Something Is Significant Using SPSS
How to Make a Monthly Budget Circle Graph
How to Solve Linear Programming in Excel
How to Find Standard Deviation on a TI 84 Plus