Correlation is the amount of similarity between two sets of data. The coefficient of correlation R ranges between -1 and 1, with 0 showing no correlation, 1 showing perfect correlation and -1 being perfect anti-correlation. R-squared -- ranging between 0 and 1 -- is often used to report the similarity between two sets of data. In business analysis, correlation can tell you the strength of the relationship between a factor and a results. For example, marketing spending and sales may be strongly correlated and have a high R-squared value. The correlation coefficient can be simply calculated in Microsoft Excel.
Step 1
Open an existing spreadsheet in Microsoft Excel containing the data you wish to correlate. You will be correlating the numbers in one data column to another.
Video of the Day
Step 2
Select an empty cell to contain the calculated correlation coefficient R. Create a label above this cell to distinguish it from data cells.
Step 3
Enter the formula "=CORREL(array 1,array 2)" in the formula box with the Correlation Coefficient cell selected. Change "array 1" and "array 2" to the corresponding ranges of the two sets of data you wish to correlate. Note that these must be of the same length. For example:
=CORREL(A2:A7,B2:B7)
Step 4
Select a new cell to contain the R-squared value calculated from the R value. Enter the formula "=POWER(cell,2)" into the formula box with the cell selected, changing "cell" to the cell containing the correlation coefficient. For example:
=POWER(C2,2)
Video of the Day