Define an array based on calculations

[Zammy-Sam]

Hi,

well, I just miserably failed in posting my first thread. Hope I will
be luckier this time.

I have a couple of matrices (170) in one excel sheet and would like to
calculate the correlation coefficient of all pair combinations. That
will give me about 14000 correlation calculations.
I already specified the pair combinations, where each matrix got the
label 1, 2, 3... 170 and arranged in two different columns (AB, AC)
like this:
1 2
1 3
..
65 102
..
169 170
All matrices are in the area of A1 to T4076, where each matrix has
20x20 cells with 4 empty rows between eachother.

Basically, I need to manage such a forumula:
'=CORREL($A($AB1*24):$T($AB1*24+20);$A($AC1*24):$T($AC1*24+20))'
Unfortunately, $A($AB1*24) is not a proper array definition in excel.

So, how do I manage to define a line that considers the current pair in
the columns AB and AC and based on the values in AB and AC retrieves the
areas for the desired 20x20 matrix to calculate the correlation
coefficient?
I hope I was able to make myself clear and hope this wasn't asked
before in this forum, since I didn't know how to search for this
matter.

Thanks a lot!

Sam

PS.: I am using the most recent MS Excel version for macs.

 

[Niek Otten]

Hi Sam,

Look in HELP for the INDIRECT() function.

 

[Herbert Seidenberg]

Assuming your dat looks like this:
seta setb corr
1 2 -0.13
1 3 0.17
.... ...
65 102 -0.02
.... ...
169 170 0.33
with the columns named seta and setb and
the arrays named arr1 thru arr170,
then the formula under corr, in R1C1 style, is
=CORREL(INDIRECT("arr"&seta R),INDIRECT("arr"&setb R))
If naming the arrays is too much work, try this formula instead
=CORREL(INDEX(arra,(seta-1)*(arr_s+arr_d)+1,1):
INDEX(arra,(seta-1)*(arr_s+arr_d)+arr_s+1,arr_d),
INDEX(arra,(setb-1)*(arr_s+arr_d)+1,1):
INDEX(arra,(setb-1)*(arr_s+arr_d)+arr_d,arr_d))
where arra is R1C1:R4076C20 (A1:T4076),
arr_d is 20 and arr_s is 4

 

[Zammy-Sam]

Dear Niek Otten and Dear Herbert Seidenberg,

your input helped me out. Thank you very much!
The final formula is:
'=CORREL(INDIRECT("A"&(24*$AB1-23)):INDIRECT("T"&(24*$AB1-4));INDIRECT("A"&(24*$AC1-23)):INDIRECT("T"&(24*$AC1-4)))'

Cheers

Sa