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 start-up 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 and CORREL functions. We will input two of our columns in the CORREL 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.
1. DD, DE, DF, DG, DH, DI, DJ, DK, DL, ED, EE, EF, EG, EH, EI, EJ, EK, …
2. 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)
```
3. 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.

Q-1: Which two factors have the largest positive correlation (not including the diagonal)? and

Q-2: Which two factors have the largest negative correlation?

Lesson Feedback

1. Comfort Zone
2. Learning Zone
3. Panic Zone

1. Very little time
2. A reasonable amount of time
3. More time than is reasonable

1. Don't seem worth learning
2. May be worth learning
3. Are definitely worth learning

1. Definitely within reach
2. Within reach if I try my hardest
3. Out of reach no matter how hard I try

You have attempted of activities on this page