Can I "CORREL" statistical funtion with three or more Arrays?

[juanes]

I wuold like to returns the correlation coefficient using ten different
arrays but the "CORREL" statistical funcion just let me use 2 at the time.
Some body know how to run a correlation funcion over 10 different colums at
the time ?

I really appreciate your help ,

Thank You very much.

JJ

 

[John Mansfield]

JJ,,

As far as I know there is no function beyond CORREL that will allow you to
regress more than two arrays. However, it can be done with VBA.

I have a template the currently regresses up to 8 columns of data. It could
be modified to include ten. If you would like it, please go to my site's
"Contact" page and send me an email. I'll send the template to you.

 

[Harlan Grove]

John Mansfield wrote...

As far as I know there is no function beyond CORREL that will allow you to
regress more than two arrays. However, it can be done with VBA.

....

It's not regression, it's pairwise correlations.

If the OP had 10 series each with 20 observations stored in A1:J20 (so
each series in a separate column), then the correlation matrix could be
generated in, say, A25:J34 by selecting that range with A25 the active
cell, typing the formula

=CORREL(OFFSET($A$1:$J$20,0,SUM(COLUMN()-COLUMN($A$25)),,1),
OFFSET($A$1:$J$20,0,SUM(ROW()-ROW($A$25)),,1))

and pressing [Ctrl]+[Enter]. Granted there'd be 100 separate formulas
in A25:J34 of which 55 are unnecessary (since the main diagonal would
always contain nothing but 1s and the matrix will be symmetric). Still,
this is a pretty easy way to get all of them from typing a single
formula.

 

[Michael R Middleton]

JJ -

I wuold like to returns the correlation coefficient using ten different
arrays but the "CORREL" statistical funcion just let me use 2 at the time.
Some body know how to run a correlation funcion over 10 different colums
at the time ? <

Use the Correlation tool of the Analysis ToolPak. It will provide
correlations for each pair of the ten variables.

- Mike
www.mikemiddleton.com