Correlate function between two specific lookup values in a list

[umchem]

Hi Everyone,

Long time lurker.

My question is I have a long list of data in a single column, with two specific numbers in the first two top rows. I need to lookup these numbers in the list below, and find the correlation between them. The correlation is matched based on the normal counting if rows between them.

I have attached an example workbook to help the description. The idea is with more data, I can simply drag across the sheet to make the calculations.

I think it's an index, match, match scenario, but I just cant get it right. In the attached, I need to find the correlation between 0.7211 and 16.3066, against the row differences between 1 to 22.

Any help is appreciated.
Thank you all.

[Glenn Kennedy]

Try this:

=CORREL(INDEX($C$6:$C$28,MATCH($C$1,$C$6:$C$28,0)):INDEX($C$6:$C$28,MATCH($C$2,$C$6:$C$28,0)),ROW(INDIRECT("1:"&1+C3)))

[Glenn Kennedy]

NB. Probably better to use (for the sake of consistency) $C$3 rather than C3, in the above formula.

[umchem]

Glenn,

Worked out perfectly, thank you very much!!!
I struggled for awhile to even conceptualize how to do this.

Sincerely appreciate the assistance, have a great day!

[Glenn Kennedy]

You're welcome and thanks for the Rep.