This article explains how to compute a correlation matrix using an excel formula.

What is Correlation matrix?

It is simply defined as the measure of the relationship between two or more variables. Coefficients have a range of -1 to 1; -1 is the perfect negative correlation while +1 is the perfect positive correlation. It is also called multiple correlation coefficient. The post below explains how to calculate multiple correlation coefficient in Excel.

## Excel Formula to calculate Correlation Matrix

=CORREL(OFFSET(firstvariable_range,,ROWS($1:1)-1),OFFSET(firstvariable_range,,COLUMNS($A:A)-1))

**For example,**you have a 5 X 5 matrix and data for variables are entered in cells B3:F50 so paste this formula in cell J4

=CORREL(OFFSET($B$3:$B$50,,ROWS($1:1)-1),OFFSET($B$3:$B$50,,COLUMNS($A:A)-1))

In this case, firstvariable_range is B3:B50.

Download the workbook used in this example so you can copy and practice the formula.

How it works :

Let's take data for 5 variables and try to figure out the correlation between them.**1. CORREL Function**

**Usage :**It returns the correlation coefficient between two variables.

**Syntax : =**CORREL ( first variable_range, second variable_range )

For variable 1 and variable 2, the syntax would be =CORREL(B3:B50, C3:C50)

**2. OFFSET Function**

**Usage :**It returns a reference to a range that is a given number of rows and columns from a given reference

**Syntax : =**OFFSET (reference range, number of rows up or down, number of columns to the left or right)

**For example, =**OFFSET($B$3:$B$50,,1)

**means one column to the right of B3:C50 i.e. C3:C50**

**3. ROWS / COLUMNS Function**

**Usage :**It returns the number of rows or columns in a range.

**Syntax : =**ROWS ( range )

**For example, =**ROWS ($1:2) returns 2.

**OR =**COLUMNS ($A:B) returns 2

4. Dynamic CORREL Function

For variable 2 and variable 3,
=CORREL(OFFSET($B$3:$B$50,,ROWS($1:2)-1),OFFSET($B$3:$B$50,,COLUMNS($A:C)-1))i. ROWS($1:2) returns 2. Hence, ROWS($1:2)-1 returns 1

ii. OFFSET($B$3:$B$50,,ROWS($1:2)-1) returns reference of range C3:C50 i.e. data set for variable 2

iii. COLUMNS($A:C) returns 3. Hence, COLUMNS($A:C)-1 returns 2

iv. OFFSET($B$3:$B$50,,COLUMNS($A:C)-1) returns reference of variable 3

How to use it

Paste the formula below to N rows x N columns. In 5 X 5 matrix , paste down to 5 rows and right to 5 columns.
=CORREL(OFFSET($B$3:$B$50,,ROWS($1:1)-1),OFFSET($B$3:$B$50,,COLUMNS($A:A)-1))

Awesome, this saved me tons of time! Thank you for the step-by-step instructions.

ReplyDeleteThanks a lot!!

ReplyDeleteTHANK YOU VERY MUCH HELPED A LOT

ReplyDeleteThank you very helpful

ReplyDeleteThat's fantastic !!! Thank you so much !!!!

ReplyDeletecould you please write us the mathematical equation used to calculate the cross correlation coefficient.

ReplyDelete