Dynamic Correlation Calculation

[andrewc]

Hi,

I have a worksheet of data with dates in ascending order in column A, and in columns B and C there are a list of investment returns at each date for two assets:

A B C
Date Return for asset 1 Return for asset 2


I would like to create a formula that will calculate the correlation (using Excel's CORREL function) of the returns of the two assets between any two dates represented in the range. I would like the formula to refer to the start and end dates in cells d1 and e1 respectively so that I can change the dates in those two cells and have the correlation formula recalculate for the new period selected.

Can someone please help.

Thanks!

[ChemistB]

What you need to do is use "Defined Names" to create dynamic ranges and then refer to these ranges in your CORREL formula.

In my data, A2:A100 is date range, Col B is Set 1, Col C is set 2
Initial date is in E1, Closing date is in E2

My first defined name is "Set1"

=OFFSET(Sheet1!$A$2,MATCH(Sheet1!$E$1,Sheet1!$A$2:$A$100)-1,1,MATCH(Sheet1!$E$2,Sheet1!$A$2:$A$100)-MATCH(Sheet1!$E$1,Sheet1!$A$2:$A$100)+1)

If you are unfamiliar with the MATCH and OFFSET functions, post your questions and I'll explain.
The second name "Set2" is much simplier.

=OFFSET(Set1,,1)

Then the correlation formula is

=CORREL(Set1,Set2)

Does this work for you?

ChemistB

[andrewc]

It works brilliantly.

Thank you very much!