Z
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.
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.