multiple correlations - help!

**cxr** · 10-22-2012, 04:34 PM

Hi guys!

I was wondering if you can help me - i am a bit of an excel novice, but am trying to learn...

I have a very long bivariate series. say, for example, 1....500000, in columns a and b.

I then want to analyze the series into blocks of 100. I want to look at correlations for each 100 pairwise observations. So, then, in cell d1, for example I want to calculate the correlation between the two series (a and b - simply correl(a1:a100,b1:b100)) for the first 100 pairs.Then, in cell e1, for example I want to calculate the correlation between the second hundred pairs, from (101,...,200 - which is obviously correl(a101:a200,b101:b200)).

Is there an easy way to do this without typing out each correlation formula - i.e. - add 100 to each argument as you copy across horizontally? Is this a VBA thing?

Many thanks for your help!

**shg** · 10-22-2012, 05:00 PM

In C1 and copy down,

=CORREL(
INDEX(A:A, 100*(ROW()-ROW(C$1))+1):INDEX(A:A, 100*(ROW()-ROW(C$1))+100),
INDEX(B:B, 100*(ROW()-ROW(C$1))+1):INDEX(B:B, 100*(ROW()-ROW(C$1))+100))

**cxr** · 10-22-2012, 05:55 PM

i am forever in your debt. thank you so much.

**cxr** · 10-22-2012, 06:14 PM

Apologies! I have just one further question - If i want to amend this for another correlation coefficeint - say - where you have C1 as your 'contemporaneous' correlation coefficient - and I would like the value in D1 to be a 'lagged' correlation coefficient - say, correl(A2:100,b1:b99), with the correlation to go into cell D1, how would I do this, again in a format which I could drag down? Trying to figure it out on my own but it is difficult as a novice!

**shg** · 10-22-2012, 06:39 PM

=correl(
index(a:a, 100*(row()-row(c$1))+2):index(a:a, 100*(row()-row(c$1))+101),
index(b:b, 100*(row()-row(d$1))+1):index(b:b, 100*(row()-row(d$1))+100))