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 every other column?
Thanks!
=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 every other column?
Thanks!
Last edited: