2.9. Case Study 2: Calculating a Correlation Matrix for Business Data¶
If you recall from the previous case study, we used a correlation matrix and all the values of 1.0 mean that each factor is perfectly correlated with itself.
The following steps are a reminder on how to make a correlation matrix between the various factors of our business startup data.
This will be a challenge as it will test your use of the $
in defining ranges, and your ability to think about having
several cells of a spreadsheet work together.
To make our correlation matrix, we will again make use of sheets
INDEX
andCORREL
functions. We will input two of our columns in theCORREL
function to compute the correlation between them. Remember that for a correlation matrix, we need to compute the correlation between all pairs of columns. Like in the previous case study, we can use the column letters.DD, DE, DF, DG, DH, DI, DJ, DK, DL, ED, EE, EF, EG, EH, EI, EJ, EK, …
Here is a Python snippet that should give you the full idea.
for i in "DEFGHIJKL": for j in "DEFGHIJKL": print("correlate column ", i, " with column ", j)
The aptly named
INDEX
function allows us to do this by writing one clever function and then copying and pasting it to fill out our matrix.
You have been successful if you end up with a matrix where the diagonal is 1.
3. You can make a heatmap using conditional formatting once you have the numbers. You can play around with conditional formatting rules. e.g. Change the background color of the cells.
You can add other rules to cover different ranges, but you will immediately see which cells we might want to focus on the most.
Q1: Which two factors have the largest positive correlation (not including the diagonal)? and
Q2: Which two factors have the largest negative correlation?
Lesson Feedback

During this lesson I was primarily in my...
 1. Comfort Zone
 2. Learning Zone
 3. Panic Zone

Completing this lesson took...
 1. Very little time
 2. A reasonable amount of time
 3. More time than is reasonable

Based on my own interests and needs, the things taught in this lesson...
 1. Don't seem worth learning
 2. May be worth learning
 3. Are definitely worth learning

For me to master the things taught in this lesson feels...
 1. Definitely within reach
 2. Within reach if I try my hardest
 3. Out of reach no matter how hard I try