correl() function - create matrix??

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

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
 
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
 
Alan Beban said:
What's supposed to be in C19 again? C20? C21?
Click to expand...

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).
 
Many thanks Harlan,
Works like magic.
Your time and help are much appreciated.
Regards
Mark
 
Back
Top