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.
Calculate twice to ensure that you have calculated the value of the correlation coefficient correctly.
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.
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
Warnings
References
- "Statistics Alive;" Wendy J. Steinberg; 2008
- "Statistics;" Robert S. Witte; 1980
- "Statistics for People Who Think They Hate Statistics, Excel 2007 Edition;" Neil J. Salkind; 2009
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.
About the Author
Liaqat Ali is a freelance writer and an adjunct faculty at University of San Francisco. Liaqat has a bachelor of arts degree from the University of California at Berkeley in Social Sciences with an emphasis in Education and managment. He also holds Masters in Information Systems from the University of San Francisco. His writings have appeared at ehow.com, Associated Content and Examiner.com.