Cross-Correlation in Excel

[Remois]

Hi all,

How can we analyze the cross-correlations (8 lags, 8 leads) between variable B and the 4 other variables (C, D, E, F)?

Variable A represents the date of the corresponding observation. We would like to calculate the cross correlation of variable B versus the remaining 4 variables (i.e. C, D, E, F). We tried to solve this issue with =CORREL(OFFSET(

Dataset structure (example data):


[TABLE="width: 501"]
<colgroup><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]Lags[/TD]
[/TR]
[TR]
[TD]1995-Q1[/TD]
[TD][/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1995-Q2[/TD]
[TD="align: right"]0.01%[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]-8[/TD]
[/TR]
[TR]
[TD]1995-Q3[/TD]
[TD="align: right"]0.02%[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]-7[/TD]
[/TR]
[TR]
[TD]1995-Q4[/TD]
[TD="align: right"]0.03%[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD]1996-Q1[/TD]
[TD="align: right"]0.04%[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]1996-Q2[/TD]
[TD="align: right"]0.05%[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]-4[/TD]
[/TR]
[TR]
[TD]1996-Q3[/TD]
[TD="align: right"]0.06%[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]1996-Q4[/TD]
[TD="align: right"]0.07%[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD]1997-Q1[/TD]
[TD="align: right"]0.08%[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]1997-Q2[/TD]
[TD="align: right"]0.09%[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]1997-Q3[/TD]
[TD="align: right"]0.10%[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]1997-Q4[/TD]
[TD="align: right"]0.11%[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]1998-Q1[/TD]
[TD="align: right"]0.12%[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]1998-Q2[/TD]
[TD="align: right"]0.13%[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]1998-Q3[/TD]
[TD="align: right"]0.14%[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]1998-Q4[/TD]
[TD="align: right"]0.15%[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]1999-Q1[/TD]
[TD="align: right"]0.16%[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]1999-Q2[/TD]
[TD="align: right"]0.17%[/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


In the end, the solution should look like this:

C D E F

Lag-8 X X X X
Lag-7 X X X X
Lag-6 X X X X
Lag-5 X X X X
Lag-4 X X X X
Lag-3 X X X X
Lag-2 X X X X
Lag-1 X X X X
Lag0 X X X X
Lag1 X X X X
Lag2 X X X X
Lag3 X X X X
Lag4 X X X X
Lag5 X X X X
Lag6 X X X X
Lag7 X X X X
Lag8 X X X X

Many thanks for your support.

 

[Gerald Higgins]

Hi, welcome to the board.

I have absolutely no idea what you want to do here.
Please can you explain more clearly ?
Don't worry too much about explaining what your data means in the real world, just focus on exactly what data you have now, and what exactly do you want to do with it.

Are your variables C, D, E and F really all identical on each row ?
If NO, what do they REALLY look like ?

Do you really want a table full of "X" characters
If NO, what do you REALLY want ?
If you want numbers, what exactly should those numbers be, for the sample data you post ?

 

[Remois]

Hi Gerald,

Thank you for your prompt reply.

Regarding your first question: the values in columns C to F represent sample data (large figures, we just used simplification).

Regarding your second question: the values 'X' should represent the cross correlations of each variable (i.e. C, D, E and F) with respect to variable B. This is what we want to get in the end. I have tried to solve this issue by applying the function =CORREL(OFFSET(...),OFFSET(..)), but failed each time.

I hope, you understand my clarifications.

Regards