Fill Formula Alternating Columns?

H

hcour

Board Regular
Joined
Feb 28, 2014
Messages
50
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!
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
In my experience, the best language to write matrix algebra functions is in "C". I will look through some of my past work and get back to you ASAP. I am familiar with this topic so hopefully I can help you. Celestial
 
Upvote 0
Hi

Welcome to the MrExcel Forum.

What columns are the market data in?

Is it columns B through BJ?
 
Upvote 0
Hi, thank you.

Sorry, I'm not sure yet exactly how many markets I'll use, about 60. They will be on a worksheet entitled DATA and start on column B. The matrix will be on a worksheet entitled, appropriately enough, MATRIX.
 
Upvote 0
I am still looking into this as promised. Off the top of my head I would suggest using an Array variable. Type in "Array", Excel VBA, and whatever version of VBA you are using into any search engine. You can write public functions performing math on these arrays, but I'm not sure you need that. Send me your whole spreadsheet, and I will fix it for you I promise. Celeste
 
Upvote 0
Hi

This offset formula :-
Code: 
=OFFSET($B$3,,INT((COLUMNS($B:B)-1)/2),5+ISODD(COLUMNS($B:B))*45,1)[code]

give you the scope to reference 50 rows from B3 in every odd numbered column from where you first place the formula and 5 rows in every even numbered column.

Then in third column it will reference column C and the fourth column it will also reference column C.

Which is what I assume is the way you want to create your matrix.

hth

@celestial Please be aware of rules 18 and 19 in 
http://www.mrexcel.com/forum/excel-questions/127080-guidelines-posting-updated-22-nov-2006-a.html
 
Upvote 0
Thank you very much. I'm sorry, I know it's frustrating to deal with a total noob, but I'm not sure how to implement your formula. In my test correlation matrix my data is in column B to column I on worksheet DATA, so would I just paste your formula into a cell with CORREL function in front of it, then fill down and to the right? Something like this?

=CORREL(OFFSET(DATA!$B$3,,INT((COLUMNS(DATA!$B:B)-1)/2),5+ISODD(COLUMNS(DATA!$B:B))*45,1))
 
Upvote 0
Hi

But don't you need two arrays for the CORREL function.

I would assume that Post#7 would be the right hand part.

If you could give a small snapshot of your data of columns B, C, D and E and your expected results from the formula, it could give me an insight to your final objective.

Thanks
 
Upvote 0
Excel%201.gif
Excel%202.gif
Excel%203.gif
Here you go. First image is my DATA sheet, second image is the MATRIX sheet, a test matrix I created just manually entering the formulas using data from the DATA sheet and is how I want the final matrix to look. Third image is another test matrix, this one with the data on the same sheet. On this sheet, using the offset function and filling down and to the right, I first created two separate matrices, a 50 day and a 5 day, then cut the columns from the 5 day matrix and inserted them to the right of the columns in the 50 day matrix.

Thanks!
 
Upvote 0
Hi

Thanks.

I think you'll find this formula :-
Code: 
=CORREL(OFFSET(DATA!$B$3,,ROWS($1:1)-1,5+ISODD(COLUMNS(DATA!$B:B))*45,1),OFFSET(DATA!$B$3,,INT((COLUMNS(DATA!$B:B)-1)/2),5+ISODD(COLUMNS(DATA!$B:B))*45,1))
[code]

dragged across and then down will match the calculations in the matrix on the TEST sheet.

Good luck.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Finding the Nth Smallest Position
2 3
Replies
21
Views
443
Fluff
Fluff
T
  • Question
Copy row values from one worksheet in one workbook to multiple workbooks based on file name
Replies
0
Views
147
thechad
T
P
  • Solved
MAXIFS/MINIFS
Replies
8
Views
249
Peter_SSs
Peter_SSs
W
  • Question
I need a Formula to Autofill the date based on the Date, Specific Value, and then no of days
Replies
6
Views
136
Alex Blakenburg
A
N
  • Question
Auto Scoring Matrix
Replies
4
Views
223
Eric W
Eric W

Forum statistics

Threads
1,214,414
Messages
6,119,375
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top