I am trying to find the correlation of two columns of data if multiple conditions are met -- almost like a "sumifs" function, except a "CorrelIfs" function.
Column A = dates (dd/mm/yy)
Column B = Quarter (1-4)
Column C = Year (yyyy)
Column D = daily natural gas prices for 10 years
Column E = daily power prices for 10 years
Cell F1 = Start Year
Cell F2 = End Year
Cell F3 = Quarter
I am trying to make the correlation function be dynamic, so that I can change the start date in F1, the End Date in F2 and the Quarter in F3. Specifically, I'd like to be able to type 2015 in F1, 2017 in F2 and Q3 in F3 and get the correlation of natural gas (Column D) to power (Column E) from 2015 thru 2017 for just Q3 during that time period. Then I need to be able to change the start year and/or End Year and/or Quarter.
I can do this for changing start and stop dates, but I do not know how to introduce the changing Quarter cell. For the changing start/stop dates:
=Correl(Index($D:$D, Match ($F$1,$C:$C,0)): Index($D:$D, Match($F$2,$C:$C,0)), Index($E:$E, Match($F$1,$E:$E,0)): Index($E:$E,match($F$2,$C:$C,0))) works for me for calculating the correlations for the specific years. But I do not know how or where to introduce the changing "Quarter" variable (cell F3) into this formula.
Any help will be greatly appreciated.
Many thanks,
TK
Column A = dates (dd/mm/yy)
Column B = Quarter (1-4)
Column C = Year (yyyy)
Column D = daily natural gas prices for 10 years
Column E = daily power prices for 10 years
Cell F1 = Start Year
Cell F2 = End Year
Cell F3 = Quarter
I am trying to make the correlation function be dynamic, so that I can change the start date in F1, the End Date in F2 and the Quarter in F3. Specifically, I'd like to be able to type 2015 in F1, 2017 in F2 and Q3 in F3 and get the correlation of natural gas (Column D) to power (Column E) from 2015 thru 2017 for just Q3 during that time period. Then I need to be able to change the start year and/or End Year and/or Quarter.
I can do this for changing start and stop dates, but I do not know how to introduce the changing Quarter cell. For the changing start/stop dates:
=Correl(Index($D:$D, Match ($F$1,$C:$C,0)): Index($D:$D, Match($F$2,$C:$C,0)), Index($E:$E, Match($F$1,$E:$E,0)): Index($E:$E,match($F$2,$C:$C,0))) works for me for calculating the correlations for the specific years. But I do not know how or where to introduce the changing "Quarter" variable (cell F3) into this formula.
Any help will be greatly appreciated.
Many thanks,
TK