Correlation matrix between two data sets

[Alexander_Golinsky]

Hi all, please help,

Lets say we have two data sets: 1st consists of 100 variables arranged into 100 columns and 2nd one is 200 other variables arranged into 200 columns. We need to calculate correlations of each of 100 variables with every one out of 2nd 200 variables. All variables for the same sample of 50 dates - so per every date we have 100+200=300 variables.So no need in correlations inside the groups, only cross correlations.At the output must be 100*200=20000 correlations. Is it possible some how just to write formula using correl and just drag it?? Any other way will be welcome. Is there any elegant and efficient way of doing it, say, in 1 min??? THANKS IN ADVANCE

[Mandy11]

Post a small example.

[shg]

What are the ranges of each of the datasets?

[Alexander_Golinsky]

Date GTITL1 GTITL2 … GRITL100 Date GTESP1 GTESP2 … GRESP200
1/1/2012 0.149615161 0.374572629 … 0.348408936 1/1/2012 0.272902818 0.708433572 … 0.739978709
1/2/2012 0.675777933 0.049600082 … 0.533729676 1/2/2012 0.026408486 0.748448889 … 0.050795401
1/3/2012 0.406733691 0.49848047 … 0.225104348 1/3/2012 0.591292552 0.971468783 … 0.907372495
… … … … … … … … … …
2/19/2012 0.739873688 0.779420615 … 0.578924276 2/19/2012 0.315167013 0.603699503 … 0.992832486

[shg]

Please Login or Register  to view this content.

The formula in P2 and copied right and down is

=CORREL(INDEX(DataA, 0, P$1), INDEX(DataB, 0, $O2))

[Alexander_Golinsky]

I knew you're true Guru!!! Thank you very much!! You saved me a lot of time )

[Alexander_Golinsky]

Now the other question. What if two data sets are for the same period but number of days and days themselves differ for different data sets. This formula wont work then. How one can solve this?