Friday, December 21, 2012

How to Calculate Correlation Coefficient Between Two Data Sets


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.
2. Click on the 'A9' cell. This is the cell where you will calculate the correlation coefficient.
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.'
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.'
5. Read your result. In this example, the calculated value of the correlation coefficient is 0.298807.

Blogger news