Correlation with a lag

**simmo86** · 06-05-2012, 06:32 AM

I have two sets of data and I am trying to see if there is a correlation between the two. The Correl feature works great! but only if the data is exactly on top of it. I was wondering if there is a formula that would work out if there is a correlation even if there is a lag between the two.. i.e it would know that there is a correlation on the data below and the lag between the two:

01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00
1 2 3 4 5 6 7 6 5 4 3 2
5 6 7 6 5 4 3 2 1 1 1 1

Thanks very much for your help in advance and please feel free to tell me that this can't be done.

Many thanks,

**Søren Larsen** · 06-05-2012, 07:51 AM

Assuming your data is in rows 2 and 3, then perhaps:

=CORREL(A2:L2,B3:K3) assuming K3 is empty

**simmo86** · 06-05-2012, 01:40 PM

Hi Søren

Thanks for your reply however it doesn't seem to take the correlation "lag" into account. i.e. I was hoping there is a formula that would effectively say that there is a correlation of 1 (because they have the same up and down) but it doesnt show the lag. i.e. is it possible to have a formula that says if you move the data 4 hours then you will have a correlation of 1.

I have attached a trial book.

Thanks again,

**MarvinP** · 06-05-2012, 01:56 PM

Hi simmo86,

See if the attached helps. I simply do correl for different lags and show the result.

**Søren Larsen** · 06-05-2012, 02:15 PM

I don't think you make one formula to tell how much you need to lag in order to achieve a correlation of 1; it would quickly become cumbersome in any case. But you could alter MarvinP's formula in B5 to:

=CORREL(A2:L2,$A$3:$L$3) and copy across instead of down.

And then create a formula that returns the lag which will give you the highest correlation coefficient, and then go from there.