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]

It works great! Very cool. Thank you so much, Mike. Originally, I thought I was going to have to manually enter in the formulas for each cell for a 60x60 matrix. Talk about simplifying the process!

 

[ukmikeb]

Hi

Thanks for the feedback.

I enjoyed the challenge and was pleased to have solved your problem.

Good luck with your project.

 

[celestial]

Mike,
Hi there. Thanks for the suggestions as I am new to this board myself. I do understand what the offset code does.

If I said anything that suggested I was trying to privately contact the person with this question, then I am sorry that I falsely gave you that impression. I think that is what you meant by quoting the rules to me, because I certainly didn't at any point try to email a board member (at least I sure hope I didn't). I never told the person with this question my direct email address or any contact information, so I'm not sure how you thought I was attempting to contact him outside of this board. I thought I have seen previous quesitons and answers with files attached. That is what I meant. But I could certainly be mistaken or thinking or another website.

I wanted to look at the Excel spreadsheet and offer suggestions using Excel formulas (w/o VBA). I wasn't sure that actual matrix algebra was needed. As you know performing calculations on matrices is different than using a matrix to set up an array variable. And sometimes it is very easy and fast, and much more user friendly, to set up one worksheet in Excel in any format you want, like every third row, etc, every other column, basically whatever you want, and of course set up protections on that worksheet if needed. That is the template of what you want to actually use, view, turn in, etc.. Then you can paste in the latest "actuals" cost/finance data (as values) into another worksheet. With concatenate, Vlookup, match , if , etc... basically Excel functions combinbed, on both sheets you can accurately populate the worksheet that you need. And then add a VBA macro to analyze unique rows within the concatenated data in the cost actuals that aren't located within your template (simple ISNA, VLookup combo), they need to be added to the template. Or you can set up your template to anticipate every situation and then delete or hide rows that =0. That is what I was trying to do.

I personally am very experienced with that type of thing. I am also familiar with writing code in VBA, Visual Basic, C, and Java. If this website is supposed to contain VBA for Excel solutions only, then please let me know. I want to work within the boundaries of the website, I just thought the website was for Excel primarily, with some VBA. That's all. I was trying to help this person, and I promise my intentions were good. I'm sure it is more efficient to write code, but based on this person I was thinking that I could get him started with some Excel formula building blocks that he could use as he learns VBA. Now maybe that is different than how you would approach this problem, but that is why I was trying to offer an alternative. Again, I am very experienced with this and i promise you I'm very good at what I do. That is why I was promoted out of Finance and into Software at NASA.

Please write me back, I don't want to start off on a bad note and I feel like I have made you angry with me. I don't think it is bad to offer an alternate solution, that is all. I do want to abide by all the rules of course. But I also don't want you to be upset if I offer the user a different alternative. I felt I had to see the Excel spreadsheet to best perform my analysis, which I planned to post online on this forum.

 

[ukmikeb]

If I said anything that suggested I was trying to privately contact the person with this question, then I am sorry that I falsely gave you that impression. I think that is what you meant by quoting the rules to me, because I certainly didn't at any point try to email a board member (at least I sure hope I didn't). I never told the person with this question my direct email address or any contact information, so I'm not sure how you thought I was attempting to contact him outside of this board. I thought I have seen previous quesitons and answers with files attached. That is what I meant. But I could certainly be mistaken or thinking or another website.

I wanted to look at the Excel spreadsheet and offer suggestions using Excel formulas (w/o VBA).

@celestial
Fair enough but if you re-read Post #5 I'm sure that you'll see there was an implied request.

As both of you were "new" Members I thought it best to highlight those rules.

Many times on this forum I have seen overt requests for the spreadsheet to be sent to another member only to see the thread "die" without the final solution being posted. A situation which doesn't benefit another Member looking for a solution to a similar problem.

Having contributed to this site for nearly five years I think most Members broaden their Excel knowledge with the variety of alternative solutions offered. However, I think there are some individuals who miss out by taking the first solution suggested and never return to the site to see if further solutions have been offered.

Finally, I'm neither angry or upset with you and trust that you will return to this board in the future offering members solutions to their problems.

 

[ukmikeb]

Hi

I think I have found a better formula which won't be so heavy on processing, as follows :-

=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),,INT((COLUMNS(DATA!$B:B)-1)/2),1+ISEVEN(COLUMNS(DATA!$B:B))))

The principal is the same but it has the advantage that it has the full address of both sets of data that it references.

You could apply it to the MATRIX sheet as follows (untested) :-

=IF(ROW($B1)<int((columns(data!$b:b)-1) 2),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),,int((columns(data!$b:b)-1)="" code]
 < INT((COLUMNS(DATA!$B:B)-1)/2),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),,INT((COLUMNS(DATA!$B:B)-1)/2),1+ISEVEN(COLUMNS(DATA!$B:B)))),"")

and remove the requirement for the TEST sheet.

hth</int((columns(data!$b:b)-1)>

 

[ukmikeb]

Hi

Sorry the HTML on the board played up in that last post and missed a lot of the second formula.

Here it is :-
You could apply it to the MATRIX sheet as follows (untested) :-

=IF(ROW($B1) < INT((COLUMNS(DATA!$B:B)-1)/2),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),,INT((COLUMNS(DATA!$B:B)-1)/2),1+ISEVEN(COLUMNS(DATA!$B:B)))),"")

and remove the requirement for the TEST sheet.

hth

 

[hcour]

Hi, Mike. Wow, thanks for working on this. I shall try it out a little later when I have time.

However, I'm a little confused. I'm not sure what you mean by "remove the requirement for the TEST sheet". The TEST sheet was just a page I was using to experiment. It's not in my final Matrix workbook, which only has two sheets, the DATA sheet and the MATRIX sheet. On the DATA sheet the data starts in B2 and extends to column BE. Currently, using your OFFSET formula, I simply replaced both instances of $B$3 in the original formula with $B$2. I haven't confirmed whether all the correlations are accurate in the matrix, but the ones that I have checked seem to be. So that was the correct way to do it, right?

Thanks!

EDIT: Oh, wait, did you mean remove the requirement for formulating it the way it was in the test sheet? Probably. Sorry, misunderstood.

 

[ukmikeb]

Hi

In that case the cell references in the INDEX part of the formula will be $B$2:$BE$51 and $B$2:$BE$6.

The revised formula with the IF will only produce the cells where the value of ROW($B1) is less than
the value of INT((COLUMNS(DATA!$B:B)-1)/2) and the matrix will appear as per the MATRIX sheet.

I meant that the TEST sheet would be redundant.

hth

 

[hcour]

Hi Mike, me again. I've tried your new formula, included a screenshot. As you can see, everything appears to be shifted too far to the right. For some reason the forum won't let me post the entire code in my message but you should be able to see it in the screenshot. I believe it is correct, with just the changes you mentioned in your last post.

 

[ukmikeb]

Hi

Sorry, my mistake caused by the difficulty getting the formula to show in the post.

The formula should be :-

=IF(ROW($B1)-1 <= INT((COLUMNS(DATA!$B:B)-1)/2),"",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),,INT((COLUMNS(DATA!$B:B)-1)/2),1+ISEVEN(COLUMNS(DATA!$B:B)))))

hth