I was asked two days ago how to compute a correlation matrix using an excel formula.

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.

**Q. 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 :**

**=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

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))

You can download the working workbook by clicking on the link below -

Download the workbook

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))

You can download the working workbook by clicking on the link below -

Download the workbook

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