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.

  1. 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.

  2. 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.

  1. You can add other rules to cover different ranges, but you will immediately see which cells we might want to focus on the most.

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
You have attempted of activities on this page