Define an array based on calculations

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.

Hi Sam,

Look in HELP for the INDIRECT() function.

--
Kind regards,

Niek Otten

"Zammy-Sam" <Zammy-Sam.25jhdz_1143817203.5894@excelforum-nospam.com> wrote in message
news:Zammy-Sam.25jhdz_1143817203.5894@excelforum-nospam.com...
>
> 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.
>
>
> --
> Zammy-Sam
> ------------------------------------------------------------------------
> Zammy-Sam's Profile: http://www.excelforum.com/member.php...o&userid=33036
> View this thread: http://www.excelforum.com/showthread...hreadid=528561
>

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

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

Sam