Function to Move Correl over one column at a time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Number_Cruncher - 24 Feb 2007 07:51 GMT

I'm tring to set up a correlation matrix where a table contains the
same headings in both the x and y axis. I then use this formula to
find the correlation:

=CORREL($B$2:$B$253,B$2:B$253)

This returns 1 since it is the correlation of itself. The next column
over produces this formula:

=CORREL($B$2:$B$253,C$2:C$253)

What is the best way to write a formula that does not require me to
manually switch the B to a C for each and every row --- over 300 of
them.

=CORREL($C$2:$C$253,C$2:C$253)

Thanks for any help you can offer.

Reply to this Message

David Biddulph - 24 Feb 2007 07:59 GMT

I suggest that you look at Excel help for absolute and relative addressing,
so that you understand the meaning of the $ signs in your formula.

Signature

David Biddulph

> I'm tring to set up a correlation matrix where a table contains the
> same headings in both the x and y axis. I then use this formula to
[quoted text clipped - 14 lines]
>
> Thanks for any help you can offer.

Reply to this Message

Number_Cruncher - 24 Feb 2007 08:10 GMT

I suggest that you look at Excel help for absolute and relative
addressing,
so that you understand the meaning of the $ signs in your formula.
-

> David Biddulph

The formula that I've written works perfectly to copy from column B
over to column CX or however far the matrix goes. The problem is that
I need a way so that I do not have to change the B to C to D, etc,
when I move down one row. I'm trying to use this formula but am not
getting it right:

=CORREL(OFFSET($B$2,0,SUM(ROW()-CELL("Row",$B$253)),$A$264),OFFSET($B
$2,0,SUM(COLUMN()-CELL("Col",$B$253)),$A$264))

As to reading the help file --- I did that first, and when that
failed, I came looking for help. I certainly appreciate the fact that
for those with more expeience this is easy to do.

Reply to this Message

Number_Cruncher - 24 Feb 2007 08:24 GMT

I got it via a search of this group:

=CORREL(INDEX($B$2:$CX$253,0,MATCH($A257,$B$1:$CX$1,0)),INDEX($B$2:$CX
$253,0,MATCH(B$256,$B$1:$CX$1,0)))

Reply to this Message

David Biddulph - 24 Feb 2007 08:29 GMT

Perhaps you need to explain more clearly what you are trying to do.
In which cell do you have your original =CORREL($B$2:$B$253,B$2:B$253)
formula?
What do you want to do to that when you go across one column?
What do you want to do to the original formula when you go down one row?
OFFSET may be the right answer, but if you explain what you're looking for
we can probably help.

Signature

David Biddulph

>I suggest that you look at Excel help for absolute and relative
> addressing,
> so that you understand the meaning of the $ signs in your formula.
> -
>> David Biddulph

> The formula that I've written works perfectly to copy from column B
> over to column CX or however far the matrix goes. The problem is that
[quoted text clipped - 8 lines]
> failed, I came looking for help. I certainly appreciate the fact that
> for those with more expeience this is easy to do.

Reply to this Message

Number_Cruncher - 25 Feb 2007 01:50 GMT

Again, I searched this forum and found the exact answer to my question
which is the formula:

=CORREL(INDEX($B$2:$CX$253,0,MATCH($A257,$B$1:$CX$1,0)),INDEX($B
$2:$CX
$253,0,MATCH(B$256,$B$1:$CX$1,0)))

For those wanting to produce a correlation matrix manually the above
will do it perfectly.

Thanks anyway!

Reply to this Message

David Biddulph - 25 Feb 2007 10:21 GMT

I'm interested as to what you've got in the extra cells you didn't mention
earlier, such as A257, B256, and column 1. I guess that these are your row
and column labels?
Your original question was about moving references across one column at a
time, but you didn't clarify what you wanted moving where. IYou were trying
to use OFFSET, and I said that I thought it ought to do the trick.

Perhaps I can try to guess what you're aiming for?

If B257 is the cell where you've got
=CORREL($B$2:$B$253,B$2:B$253)
you've found that if you copy that to C257 you get
=CORREL($B$2:$B$253,C$2:C$253)
and then in D257 you get
=CORREL($B$2:$B$253,D$2:D$253)

I'm now guessing that what you may have been trying to ask for was how to go
down to cell B258 and get
=CORREL($C$2:$C$253,B$2:B$253) , and so on downwards, trying to increment
the *column* reference in the first part of your formula as you increased
the *row* in which your formula was sitting.

In that case, then I think you were right trying to use OFFSET, and what you
needed may have been something like
=CORREL(OFFSET($B$2,0,ROW()-ROW($B$257),252,1),B$2:B$253), but for some
reason I get a #N/A from that, though I get the right answer if I replace
ROW()-ROW($B$257) by zero.

Signature

David Biddulph

> Again, I searched this forum and found the exact answer to my question
> which is the formula:
[quoted text clipped - 7 lines]
>
> Thanks anyway!

Reply to this Message

Mike Middleton - 25 Feb 2007 00:09 GMT

Number_Cruncher -

As an aside, since you're replicating the results of the Correlation tool in
Excel's Analysis ToolPak, I checked in Excel 2007 to see if it could handle
"over 300 of them," just in case you were referring to the number of
variables instead of the number of values for each variable. I can report
that the Correlation tool of the Excel 2007 Analysis ToolPak will produce
pairwise correlations for 301 variables.

- Mike
http://www.mikemiddleton.com

Reply to this Message