multiple correlations with one click?

[bondjel]

I have a numerical data set with 11 columns and 1809 rows. I would like to
simply obtain a table of correlations involving Col 1 with each remaining
Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then
Col 4 with each remaining Col (5 thru 11), and so on until the last
correlation, i.e., Col 10 with Col 11.

 

[Otto Moehrbach]

I know what a correlation is but I have no idea what you want. Define
"correlation" in your context. IOW, what do you want to see as the result?
HTH Otto

 

[bondjel]

Think of two columns of numbers: in col 1 you have body weights and in col 2
you have no. of calories eaten per day. You want to know if those who eat a
greater no. of calories per day have higher weights. You can use either
PEARSON or CORREL to obtain the degree to which higher weights go with
greater no. of calories eaten. Now imagine you have 11 columns of numbers and
you want to do a correlation between each col and every other col. You could
laboriously go through and apply PEARSON or CORREL to cols 1 and 2, then 1
and 3, then 1 and 4, and so on. I bet there's a way to tell Excel to
calculate each and every 2 col correlation at one time and that's what I'm
looking for. Thanks for any help.

 

[Dana DeLouis]

Think of two columns of numbers: in col 1 you have body weights and in col 2
you have no. of calories eaten per day. You want to know if those who eat a
greater no. of calories per day have higher weights. You can use either
PEARSON or CORREL to obtain the degree to which higher weights go with
greater no. of calories eaten. Now imagine you have 11 columns of numbers and
you want to do a correlation between each col and every other col. You could
laboriously go through and apply PEARSON or CORREL to cols 1 and 2, then 1
and 3, then 1 and 4, and so on. I bet there's a way to tell Excel to
calculate each and every 2 col correlation at one time and that's what I'm
looking for. Thanks for any help.

Hi. Would this work?
This assumes your data starts in A1, and writes data to Columns 13-15

Sub Demo()
Dim Tbl
Dim a As Long 'First Column
Dim b As Long 'Second Column
Dim R 'Row

R = 1
Set Tbl = [A1].Resize(1809, 11)
With WorksheetFunction
For a = 1 To 10
For b = a + 1 To 11
Cells(R, 13) = a
Cells(R, 14) = b
Cells(R, 15) = .Correl(Tbl.Columns(a), Tbl.Columns(b))
R = R + 1
Next b
Next a
End With
End Sub


= = = = = = =
HTH :>)
Dana DeLouis

 

[Mike Middleton]

bondjel -

The Correlation tool of the Analysis ToolPak (provided with all versions of
Excel) creates a table of all pairwise correlations.

That seems to be exactly what you want.

- Mike
http://www.MikeMiddleton.com



I have a numerical data set with 11 columns and 1809 rows. I would like to
simply obtain a table of correlations involving Col 1 with each remaining
Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11),
then
Col 4 with each remaining Col (5 thru 11), and so on until the last
correlation, i.e., Col 10 with Col 11.

 

[bondjel]

Mike,

You know, I somehow got it to do this the other day but when I tried to
repeat my steps this AM I couldn't get it to do it. Can you tell me what I
should highlight and what specific steps I need to to complete to get it to
work?

Jim

 

[bondjel]

I didn't realize that the "Correlation tool" which is accessed thru the
Analysis Toolpack is any different from the Correl function in regular Excel.
The former does give my matrix.