 • 512K Members
• 7,620 Online
• 609K Conversations

# 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

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

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

# 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
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies