correl() function - create matrix??

[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

 

[Mark]

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]

What's supposed to be in C19 again? C20? C21?

Alan Beban

 

[Harlan Grove]

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).

 

[Mark]

Many thanks Harlan,
Works like magic.
Your time and help are much appreciated.
Regards
Mark