# Correlation, Rows, Columns, Index, Array?

#### 95helsby

##### New Member
Hi,

I'll try and explain this as best as I can.

I have a data set with say 5 companies across the top and then data for the next 5days underneath the company headings.
On the next sheet I want to show the cross correlation of this data for each company combination. I know how to do this and so on this small scale I can simply change the columns that the correlation should relate to, but on a bigger scale say 100 companies I need a formula that I'm able to drag down and across easily.

My current formula looks like this: =CORREL('logs (5yr)'!\$D\$5:\$D\$265,'logs (5yr)'!E\$5:E\$265)

When I drag this accross it works fine as the column changes but when I drag it down I need the first part of the formula to change to column E, hence the first one would be 1.00 since it's correlating against itself.

I hope this makes sense.

Thanks in advance for any help.

Mark

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Andrew Fergus

##### MrExcel MVP
Hi Mark

I got this to work as follows. Here is a copy of the raw data (range A1:I18 with column B empty):

 Raw Data: Date Co 1 Co 2 Co 3 Co 4 Co 5 Co 6 1 Jan 12 10 50 100 50 98 82 2 Jan 12 12 54 108 46 97 83 3 Jan 12 13 54 108 46 96 84 4 Jan 12 13 55 110 45 95 85 5 Jan 12 12 56 112 44 94 86 6 Jan 12 10 56 112 44 93 87 7 Jan 12 12 56 112 44 92 88 8 Jan 12 11 55 110 45 91 89 9 Jan 12 12 54 108 46 90 90 10 Jan 12 13 54 108 46 89 90 11 Jan 12 15 55 110 45 88 90 12 Jan 12 16 55 110 45 87 90 13 Jan 12 17 55 110 45 86 89 14 Jan 12 18 56 112 44 85 88 15 Jan 12 20 56 112 44 84 87 16 Jan 12 20 57 114 43 83 86 17 Jan 12 20 56 112 44 82 85

<TBODY>
</TBODY>

And a summary analysis that looks like this (range K1:T8 on the same sheet):
 Max Date : 17/01/2012 Cross-Correlations Last Row : 18 Key Co 1 Co 2 Co 3 Co 4 Co 5 Co 6 1 Co 1 1.00 0.53 0.53 -0.53 -0.88 0.13 2 Co 2 0.53 1.00 1.00 -1.00 -0.60 0.36 3 Co 3 0.53 1.00 1.00 -1.00 -0.60 0.36 4 Co 4 -0.53 -1.00 -1.00 1.00 0.60 -0.36 5 Co 5 -0.88 -0.60 -0.60 0.60 1.00 -0.50 6 Co 6 0.13 0.36 0.36 -0.36 -0.50 1.00

<TBODY>
</TBODY>

The formulas used are:
L1: =MAX(C:C)
this returns the last date entered into column C (change to suit your data) for the purposes of finding the last row in the next formula:
L2: =MATCH(L1,C:C)
this returns the last row number where there is a date on column C, and assumes a) you are using a date/time field somewhere, b) each date/time field is unique. and c) the dates/times are in ascending order.

Enter the company values in cells O2:T2 and N3:N8 per my example above. These could be linked to the raw data.

In cell M3 enter the formula:
=MATCH(\$N3,\$D\$1:\$I\$1)
and copy down as I have (see the column titled "Key"). This returns the column number (within the data range of D:I) of the company name in cell N3.

Then in cell O3 enter the following formula:
=CORREL(INDEX(\$D\$2:\$I\$2,1,\$M3):INDEX(\$D:\$I,\$L\$2,\$M3),INDEX(D:D,ROW(\$C\$2)):INDEX(D:D,\$L\$2))
and copy down and across. As you can see from my example above there are the values 1 in the top-left to bottom-right diagonal which shows each company has a correlation of 1 with itself. Further proof the formula works is the perfect negative correlation between "Co 2" and "Co 4" where the two values sum to 100, and the perfect correlation between "Co 2" and "Co 3" where the value for "Co 3" is double that of "Co 2", and so on.

Within the CORREL formula in cell O3, this part:
INDEX(\$D\$2:\$I\$2,1,\$M3):INDEX(\$D:\$I,\$L\$2,\$M3)
returns the range D2:D18. As this is copied down it references E2:E18 and so on, and doesn't change as you copy the formula across.

And this part:
INDEX(D:D,ROW(\$C\$2)):INDEX(D:D,\$L\$2))
returns the range D2:D18. As this is copied across it references E2:E18 and so on, and doesn't change as you copy the formula down.

I trust this helps.
Andrew

Last edited:

Replies
14
Views
439
Replies
20
Views
304
Replies
3
Views
118
Replies
4
Views
194
Replies
2
Views
259