Correlation

 

The correlation coefficient (a value between -1 and +1) tells you how strongly two variables are related to each other. We can use the CORREL function or the Analysis Toolpak add-in in Excel to find the correlation coefficient between two variables.

– A correlation coefficient of +1 indicates a perfect positive correlation. As variable X increases, variable Y increases. As variable X decreases, variable Y decreases.

Perfect Positive Correlation in Excel

– A correlation coefficient of -1 indicates a perfect negative correlation. As variable X increases, variable Z decreases. As variable X decreases, variable Z increases.

Perfect Negative Correlation in Excel

– A correlation coefficient near 0 indicates no correlation.

To use the Analysis Toolpak add-in in Excel to quickly generate correlation coefficients between multiple variables, execute the following steps.

1. On the Data tab, in the Analysis group, click Data Analysis.

Click Data Analysis

Note: can’t find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

2. Select Correlation and click OK.

Select Correlation

3. For example, select the range A1:C6 as the Input Range.

Select the Input Range

4. Check Labels in first row.

5. Select cell A8 as the Output Range.

6. Click OK.

Input and Output Options

Result.

Correlation Coefficients

Conclusion: variables A and C are positively correlated (0.91). Variables A and B are not correlated (0.19). Variables B and C are also not correlated (0.11)
. You can verify these conclusions by looking at the graph.