+ Reply to Thread
Results 1 to 7 of 7

Correlation matrix between two data sets

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    Correlation matrix between two data sets

    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
    Last edited by Alexander_Golinsky; 04-24-2012 at 05:25 PM.

  2. #2
    Forum Contributor
    Join Date
    08-11-2004
    Posts
    109

    Re: Correlation matrix between two data sets

    Post a small example.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Correlation matrix between two data sets

    What are the ranges of each of the datasets?
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Correlation matrix between two data sets

    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

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Correlation matrix between two data sets

           -A-- --B--- --C--- --D--- --E--- --F--- G -H-- --I--- --J--- --K--- --L--- --M--- N --O-- ---P--- ---Q--- ---R--- --S--- ---T---
       1   Date DataA1 DataA2 DataA3 DataA4 DataA5   Date DataB1 DataB2 DataB3 DataB4 DataB5   B \ A    1       2       3      4       5   
       2   xxx      66     82     79     58     22   xxx      25     96     87     23     22       1 -0.1886  0.0665 -0.1895 0.2445  0.1323
       3   xxx      66     32     65     40     19   xxx      25     51     52      4     42       2  0.0584  0.2735 -0.0047 0.2515 -0.4209
       4   xxx      13     87     21     27     81   xxx       6     46     71     11     36       3 -0.0881  0.1544 -0.1997 0.2317  0.1455
       5   xxx       5     81      1     91     42   xxx      86     98     42     91     16       4 -0.0934  0.1953 -0.1181 0.1204 -0.2265
       6   xxx      64     40     73     37     19   xxx      30     73     18     81      2       5  0.0404 -0.1189  0.2911 0.4868  0.1609
       7   xxx      58     41     13     94     55   xxx      97     92     81     14     76                                               
       8   xxx      68     90     98     93     48   xxx      28     97     53     49     55                                               
       9   xxx      28      5     46     58     84   xxx      56     21      9     20     24                                               
      10   xxx      94     47     96     43     83   xxx      60     86     41     57     88                                               
      11   xxx      75     34     81      6     77   xxx      38     26     15     12      0                                               
      12   xxx      12     24     63     40     39   xxx       0     96     95     52     88                                               
      13   xxx      92     88      2     14     29   xxx      34     76     28     78     17                                               
      14   xxx      14     34     45     39     42   xxx      61      6     18     18     44                                               
      15   xxx      11     94     16     34     88   xxx      97     30     97     58     26                                               
      16   xxx      91     43     47     87     84   xxx       3     12     91     23     94                                               
      17   xxx      19     38     99     95     45   xxx      82     38     34     62     96                                               
      18   xxx      80     35     65     43     54   xxx      98     12     57     39     40                                               
      19   xxx      45      2     42     58     62   xxx      21     83     97     72      8
    The formula in P2 and copied right and down is

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

  6. #6
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Correlation matrix between two data sets

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

  7. #7
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Correlation matrix between two data sets

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1