Correlation of every n rows

Copper Contributor

I have data in two columns, and I need to calculate correlations of first 12 rows and the last 12 rows. In this example, I need to calculate the correlations between B4:B15 and H4:H15, and B16:B27 and H16:H27. 

Let's say I write the formula in cell K5. How should the formula be written, so that the first value is equal to correlation(B4:B15,H4:H15) and when dragged down, the second value is equal to correlation(B16:B27,H16:H27). 

 

Thank you for the help. 

 

1 Reply

@Janispetke10 

In K5:

=CORREL(INDEX(B:B,12*(ROW(K5)-ROW($K$5))+4):INDEX(B:B,12*(ROW(K5)-ROW($K$5))+15),INDEX(H:H,12*(ROW(K5)-ROW($K$5))+4):INDEX(H:H,12*(ROW(K5)-ROW($K$5))+15))