I am creating a correlation matrix of 60 futures markets closing prices. I want to have two columns for each market, first column a 50 day correlation and the second column 5 day correlation. I'm an Excel novice but I found an offset formula through google and adapted it for my needs: =CORREL(OFFSET($B$3:$B$52,,ROWS($1:1)-1),OFFSET($B$3:$B$52,,COLUMNS($A:A)-1)) =CORREL(OFFSET($B$3:$B$7,,ROWS($1:1)-1),OFFSET($B$3:$B$7,,COLUMNS($A:A)-1)) Experimenting with creating this matrix, right now the only way I know how to do what I want is to create two separate matrices first, the 50 day and 5 day. (I put the formula for each in a cell, then fill down and to the right.) Then I cut the column for each market from the 5 day matrix and insert it next to the market's corresponding column in the 50 day matrix. I don't mind doing it this way since I only have to create the matrix once, but I was wondering if there was an easier way. Is there any way I can do this, by filling down and to the right for[I] every other column[/I]? Thanks!
This question generated 25 answers. To proceed to the answers, click here.
This thread is current as of February 28, 2014.