• 542K Members
• 3,890 Online
• 645K Conversations

Highlighted
New Contributor

Use of ranges in an array formula

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

5 Replies
Highlighted

Re: Use of ranges in an array formula

I'd stick with placing the original CORREL function in a column from row 2-100 and adding a SUM at the bottom. This is likely to perform better as well.
Highlighted

Re: Use of ranges in an array formula

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.

Highlighted

Re: Use of ranges in an array formula

Have you got some sample data?
Highlighted

Re: Use of ranges in an array formula

For trying things out I used =rand() in all cells of range B1:G100, copied and paste values.

Re: Use of ranges in an array formula

I've tried a couple of variations but it doesn't seem to be possible in just one array formula.
Related Conversations
Array Formulas Syntax - Matrix Calculations
ignacioch in Excel on
0 Replies
Leap year formula
Hattsoff in Excel on
4 Replies
FORMULAS
aayushman_mishra in Excel on
4 Replies