Calculating correlation with spreadsheets
Let’s look at an example.
Consider these two lists of data in an Excel spreadsheet:
To determine the correlation between them:
-
Select a blank cell at the bottom of column B and enter the formula: =CORREL(A2:A7, B2:B7) where A2:A7, B2:B7 represent the range of data to include.
-
Click Enter. Excel calculates the correlation coefficient.
According to the previous section’s diagram this would be a strong correlation value.
Tableau also provides a handy way to calculate correlation using the function CORR() to calculate the Pearson Correlation Coefficient which can then be used to create a correlation value matrix: Link
Which produces this:
Note: Image produced using Tableau.
This matrix helps with shopping basket analysis. When one category of product was bought, what other product categories were also bought in the same order. Any cells highlighted in darker blue would indicate a moderate to strong positive correlation and orange cells indicate a weak negative correlation
Next, there is a quiz where you can reflect on what you know about direct and indirect relationships in regard to coorelation so far.
