Fill Formula Alternating Columns?

[hcour]

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!

 

[hcour]

Almost there, Mike. Not sure if you can see the whole formula when I post this. I believe it's correct. If you want me to PM it to you, let me know.

 

[hcour]

Oops. I see I left the wrong cell highlighted in the screenshot, so I'll PM if you need to see it.

 

[ukmikeb]

Hi

I think I've found the solution :-

=IF(ROUND(COLUMNS($B:B)/2,0) < ROW($B1),"",CORREL(INDEX((DATA!$B$3:$I$52,DATA!$B$3:$I$7),,ROW($B1),1+ISEVEN(COLUMNS(DATA!$B:B))),INDEX((DATA!$B$3:$I$52,DATA!$B$3:$I$7),,ROUND(COLUMNS($B:B)/2,0),1+ISEVEN(COLUMNS(DATA!$B:B)))))

Apologies, I left the Column offset in the second half of the CORREL array and that's why it was giving the #NA because it was referencing column zero.

I've changed the test for generating blank cells to bring it in line with the cell referencing methods.

hth

 

[hcour]

He shoots, he scores! Seems to be working great, Mike. Perfect.

I'm very excited to start using this correlation matrix. Thanks so much for all the work you put into this and helping me out.

Harold

 

[celestial]

Hi! Thanks for the clarification, and I again apologize for any misunderstanding. I thought that this website allowed users to attach files to their posts, which would be public. I realize now I must have been confused with another website. As you pointed out yourself, I am very new, I think I was a member for like 3 hours when I tried to help this person. That is what I meant, I thought I could review the spreadsheet posted publically and then post any solution I came up with publicly. But thanks, I will keep this situation in mind and make sure to be more careful. Thanks for your help, and it won't happen again.