If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
correl() function - create matrix??
I am using the correl() function and I have a spreadsheet that I want to
create a correlation matrix. There a many columns and rows of data. Rather than use Correlation in the ToolPak, I want it to be dynamic in that when I add new data it provides an updated matrix.(I'm presently experimenting with a small dataset for now.) A16 =COUNT(B4:B15) - B column is the date column In the top left of the matrix (C18) I have the formula: =CORREL(OFFSET(C4,0,0,$A$16,1),OFFSET(C4,0,0,$A$16 ,1)) It works fine and gives me the correct result = 1 But when I copy it across, and down to create the matrix, of course it does not work for the rest.Column D (D18) should be =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) Column C (C19) should be =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) etc etc. Copying them quickly is the problem. I have been called away mid stream. Does anyone have any further tricks to easily create my Correlation matrix? ThanksMark |
#2
|
|||
|
|||
correl() function - create matrix?? - fixed formatting
Sorry about the formatting, I'll try again:
I am using the correl() function and I have a spreadsheet that I want to create a correlation matrix. There a many columns and rows of data. Rather than use Correlation in the ToolPak, I want it to be dynamic in that when I add new data it provides an updated matrix. (I'm presently experimenting with a small dataset for now.) A16 =COUNT(B4:B15) - B column is the date column In the top left of the matrix I have the formula: =CORREL(OFFSET(C4,0,0,$A$16,1),OFFSET(C4,0,0,$A$16 ,1)) It works fine and gives me the correct result = 1 But when I copy it across, and down to create the matrix, of course it does not work for the rest. =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) I have been called away mid stream. Does anyone have any further tricks to easily create my Correlation matrix? Thanks Mark "Mark" wrote in message ... I am using the correl() function and I have a spreadsheet that I want to create a correlation matrix. There a many columns and rows of data. Rather than use Correlation in the ToolPak, I want it to be dynamic in that when I add new data it provides an updated matrix.(I'm presently experimenting with a small dataset for now.) A16 =COUNT(B4:B15) - B column is the date column In the top left of the matrix (C18) I have the formula: =CORREL(OFFSET(C4,0,0,$A$16,1),OFFSET(C4,0,0,$A$16 ,1)) It works fine and gives me the correct result = 1 But when I copy it across, and down to create the matrix, of course it does not work for the rest.Column D (D18) should be =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) Column C (C19) should be =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) etc etc. Copying them quickly is the problem. I have been called away mid stream. Does anyone have any further tricks to easily create my Correlation matrix? ThanksMark |
#3
|
|||
|
|||
correl() function - create matrix??
What's supposed to be in C19 again? C20? C21?
Alan Beban Mark wrote: I am using the correl() function and I have a spreadsheet that I want to create a correlation matrix. There a many columns and rows of data. Rather than use Correlation in the ToolPak, I want it to be dynamic in that when I add new data it provides an updated matrix.(I'm presently experimenting with a small dataset for now.) A16 =COUNT(B4:B15) - B column is the date column In the top left of the matrix (C18) I have the formula: =CORREL(OFFSET(C4,0,0,$A$16,1),OFFSET(C4,0,0,$A$16 ,1)) It works fine and gives me the correct result = 1 But when I copy it across, and down to create the matrix, of course it does not work for the rest.Column D (D18) should be =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) Column C (C19) should be =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) etc etc. Copying them quickly is the problem. I have been called away mid stream. Does anyone have any further tricks to easily create my Correlation matrix? ThanksMark |
#4
|
|||
|
|||
correl() function - create matrix??
"Alan Beban" wrote...
What's supposed to be in C19 again? C20? C21? Do you know what a correlation matrix is? If you did, you wouldn't have to ask this question. So why did you bother to respond? The OP's top-left cell is C18, and it contains the formula =CORREL(OFFSET(C4,0,0,$A$16,1),OFFSET(C4,0,0,$A$16 ,1)) The OP also stated that the next right and next down formulas should both be D18 and C19: =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) because correlation matrices are symmetric. However, more illumination would have been provided if the D18 formula were =CORREL(OFFSET(D4,0,0,$A$16),OFFSET(C4,0,0,$A$16)) To answer the OP, the formulas should be C18: =CORREL(OFFSET($C$4,0,SUM(ROW()-CELL("Row",$C$18)),$A$16), OFFSET($C$4,0,SUM(COLUMN()-CELL("Col",$C$18)),$A$16)) which could be filled right and down as far as needed. The reason for the SUM() calls is that ROW() always returns an array, even when it returns a single value, and array args to OFFSET, even single entry ones, can produce unusual results. Summing is the easiest way to convert these to scalars (nonarrays). |
#5
|
|||
|
|||
correl() function - create matrix??
Many thanks Harlan,
Works like magic. Your time and help are much appreciated. Regards Mark "Harlan Grove" wrote in message ... "Alan Beban" wrote... What's supposed to be in C19 again? C20? C21? Do you know what a correlation matrix is? If you did, you wouldn't have to ask this question. So why did you bother to respond? The OP's top-left cell is C18, and it contains the formula =CORREL(OFFSET(C4,0,0,$A$16,1),OFFSET(C4,0,0,$A$16 ,1)) The OP also stated that the next right and next down formulas should both be D18 and C19: =CORREL(OFFSET(C4,0,0,$A$16),OFFSET(D4,0,0,$A$16)) because correlation matrices are symmetric. However, more illumination would have been provided if the D18 formula were =CORREL(OFFSET(D4,0,0,$A$16),OFFSET(C4,0,0,$A$16)) To answer the OP, the formulas should be C18: =CORREL(OFFSET($C$4,0,SUM(ROW()-CELL("Row",$C$18)),$A$16), OFFSET($C$4,0,SUM(COLUMN()-CELL("Col",$C$18)),$A$16)) which could be filled right and down as far as needed. The reason for the SUM() calls is that ROW() always returns an array, even when it returns a single value, and array args to OFFSET, even single entry ones, can produce unusual results. Summing is the easiest way to convert these to scalars (nonarrays). |
Thread Tools | |
Display Modes | |
|
|