Calculating Correlation among multiple pairs of columns

Welcome People, Bots & Algo’s of LinkedIn. This is the Monday Edition of The Analyst.

Calculating Correlation among multiple pairs of columns.

So today I want to share with you a way to create a correlations grid

The Dataset has four columns each with 10 rows of data.

What I want to do is find the correlations for each pair of columns?

Column 1 & Dataset

We create a correlation with column1 and itself then copy and edit the formula throughout the grid.

Array 1 : A3 -A12. Array 1 should be absolute reference

Array 2 : A3 - ?A12

Column 2 Formula

Array 1 : B3 -B12. Array 1 should be absolute reference

Array 2 : A3 -A12

Column 3 Formula

Array 1 : C3 -C12. Array 1 should be absolute reference

Array 2 : A3 -A12

Column 4 Formula

Array 1 : D3 -D12. Array 1 should be absolute reference

Array 2 : A3 -A12

You can tell when you have done it right wen every column referencing itself has a perfect correlation of 1

From here we look for significance and take it from