Correlation, Rows, Columns, Index, Array?

95helsby

New Member
Joined
Jul 30, 2009
Messages
13
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
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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:</SPAN>
Date</SPAN>
Co 1</SPAN>
Co 2</SPAN>
Co 3</SPAN>
Co 4</SPAN>
Co 5</SPAN>
Co 6</SPAN>
1 Jan 12</SPAN>
10</SPAN>
50</SPAN>
100</SPAN>
50</SPAN>
98</SPAN>
82</SPAN>
2 Jan 12</SPAN>
12</SPAN>
54</SPAN>
108</SPAN>
46</SPAN>
97</SPAN>
83</SPAN>
3 Jan 12</SPAN>
13</SPAN>
54</SPAN>
108</SPAN>
46</SPAN>
96</SPAN>
84</SPAN>
4 Jan 12</SPAN>
13</SPAN>
55</SPAN>
110</SPAN>
45</SPAN>
95</SPAN>
85</SPAN>
5 Jan 12</SPAN>
12</SPAN>
56</SPAN>
112</SPAN>
44</SPAN>
94</SPAN>
86</SPAN>
6 Jan 12</SPAN>
10</SPAN>
56</SPAN>
112</SPAN>
44</SPAN>
93</SPAN>
87</SPAN>
7 Jan 12</SPAN>
12</SPAN>
56</SPAN>
112</SPAN>
44</SPAN>
92</SPAN>
88</SPAN>
8 Jan 12</SPAN>
11</SPAN>
55</SPAN>
110</SPAN>
45</SPAN>
91</SPAN>
89</SPAN>
9 Jan 12</SPAN>
12</SPAN>
54</SPAN>
108</SPAN>
46</SPAN>
90</SPAN>
90</SPAN>
10 Jan 12</SPAN>
13</SPAN>
54</SPAN>
108</SPAN>
46</SPAN>
89</SPAN>
90</SPAN>
11 Jan 12</SPAN>
15</SPAN>
55</SPAN>
110</SPAN>
45</SPAN>
88</SPAN>
90</SPAN>
12 Jan 12</SPAN>
16</SPAN>
55</SPAN>
110</SPAN>
45</SPAN>
87</SPAN>
90</SPAN>
13 Jan 12</SPAN>
17</SPAN>
55</SPAN>
110</SPAN>
45</SPAN>
86</SPAN>
89</SPAN>
14 Jan 12</SPAN>
18</SPAN>
56</SPAN>
112</SPAN>
44</SPAN>
85</SPAN>
88</SPAN>
15 Jan 12</SPAN>
20</SPAN>
56</SPAN>
112</SPAN>
44</SPAN>
84</SPAN>
87</SPAN>
16 Jan 12</SPAN>
20</SPAN>
57</SPAN>
114</SPAN>
43</SPAN>
83</SPAN>
86</SPAN>
17 Jan 12</SPAN>
20</SPAN>
56</SPAN>
112</SPAN>
44</SPAN>
82</SPAN>
85</SPAN>

<TBODY>
</TBODY>

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

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

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top