Correlation if between two dates

[tjkramer]

Hi,

Column A =daily dates (dd/mm/yyyy) for 10 years
Column B = daily natural gas prices
Column C = daily power prices
Cell D1 = Start Date
Cell D2 = Stop Date

Would like to compute correlation between power and gas for dates that fall between my Start Date and Stop Date.
Need to be able to change Start and Stop Dates to see how correlation changes

Something along the lines of a "SumIfs" function but with a "Correlifs" -- Correlifs(power,Gas, Start Date >= D1, Stop Date <=D2)

Thanks in advance for the help!

 

[JustynaMK]

Hi tjkramer,

Try using the following formula:

=CORREL(INDEX($B:$B,MATCH($D$1,$A:$A,0)):INDEX($B:$B,MATCH($D$2,$A:$A,0)),INDEX($C:$C,MATCH($D$1,$A:$A,0)):INDEX($C:$C,MATCH($D$2,$A:$A,0)))

INDEX+MATCH formulas will identify the correct data ranges for you.

 

[tjkramer]

Worked like a charm!
Many many many thanks for the quick help and precise, easy to understand answer!
You saved me a ton of time (and prevented a ton of errors)

 

[JustynaMK]

I'm very glad to hear it! Thanks & take care.