Nov 03 2021 01:17 AM
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.
Nov 03 2021 01:34 AM
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))