Aug 06 2019 11:54 AM
Hello, how could the following be done in an array formula:
=(CORREL(B1:G1,B1:G1)>0.7)+(CORREL(B1:G1,B2:G2)>0.7)+ ... + (CORREL(B1:G1,B100:G100)>0.7)
The result should be an integer between 1 and 100 (the first term will be 1).
Thank you
Aug 07 2019 07:32 AM
Aug 07 2019 08:12 AM - edited Aug 07 2019 08:13 AM
For getting all the correlations, a matrix of 100x100 correlations would be required. It gets awkward when I want to shift the input data by one column requiring another 100x100 matrix and compare the sums with the first sums, etc. I have the impression that it is not possible to include ranges in an array formula. I have tried all kind of solutions but could not get it to work. If there is a solution I would like to try it even if it is not optimal.
Aug 07 2019 08:34 AM
For trying things out I used =rand() in all cells of range B1:G100, copied and paste values.
Aug 07 2019 08:49 AM