Iterative Correlation Test

[montreal1775]

I was wondering if it is possible to run correlation test over ever
possible combination of several variables (around 40) automatically? I
so, could Excel find correlation between individual variables and ever
other possible groupings of variables? Each variable has the sam
number of rows.

Any help is appreciated

 

[N10]

"montreal1775" <[email protected]>
wrote in message

I was wondering if it is possible to run correlation test over every
possible combination of several variables (around 40) automatically? If
so, could Excel find correlation between individual variables and every
other possible groupings of variables? Each variable has the same
number of rows.

Any help is appreciated!

HI Montreal

Do you mean linear regression coefficient or is it some other statistical
measure you are after ? CHi Square ?

Best N10

 

[montreal1775]

I am looking for the linear regression coeff

 

[Mike Middleton]

montreal1775 -

The Correlation tool of Excel's Analysis ToolPak provides all pairwise
correlations.

- Mike
http://www.mikemiddleton.com

"montreal1775" <[email protected]>
wrote in message
news:[email protected]...

 

[N10]

Hi

Manual deployment of the "correl" function is fine if one only has a few
data sets to compare . In this case I understand you have 40 variable sets
which must
as individual entities, have correlation tests run against every other
set...that in something like 40^2 analysis by my reconing..is that
correct ?

If so thats a lot of typing lol....

If you want code to do this task I would be willing to write it for you
I'll need the spread sheet

Bests N10 (e-mail address removed)

 

[Mike Middleton]

N10 and montreal1775 -

The code has already been written, and it's the Correlation tool, part of
the Analysis ToolPak provided with Excel.

You do not need "manual deployment" of the CORREL worksheet function. Choose
Tools | Data Analysis | Correlation, identify your data range in the
Correlation dialog box, specify a destination, and click OK.

If you want to reinvent the Correlation tool, with 40 variables, since
CORREL(X1,X2) = CORREL(X2,X1), the number of pairwise correlations is
(40^2)/2. And, if you avoid calculating CORREL(Xi,Xi) = 1, the number of
correlation computations is ((40^2)/2)-40.

- Mike
http://www.mikemiddleton.com

 

[N10]

HI Mike

Thanks for the clarification on this Mike . Looks like motreal1775's problem
is solved and I have increased understanding


Best N10