Correlation with multiple criteria

[tjkramer]

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

 

[tjkramer]

My bad, table actually looks like this...

MrExcelForum Correlation Multi Help.xlsx
	I	J	K	L	M	N	O	P	Q
1
2	Start Year					Q1	Q2	Q3	Q4
3	End Year					Spot Peak NY-J	Spot Peak NY-J	Spot Peak NY-J	Spot Peak NY-J
4				Q1	Spot Peak PJM
5				Q2	Spot Peak PJM
6				Q3	Spot Peak PJM
7				Q4	Spot Peak PJM
Data

 

[DRSteele]

Let's try this. It uses some rather lengthy IF statements to figure out what years, quarters and/or months you want to analyse.

Put logical numbers into the matrix (or no numbers at all into the years, quarters and/or months fields) and it should work. I know your sample data here only as one year, but this method should handle most permutations.

If you just want year 2010, then put 2010 in both Start and End. If you put 2010 in start and leave end blank, the algorithm will assume 2010 through the maximum number year in the data. The same goes for the quarters and months fields as long as the start value is not greater than the end value. If you put 2016 in end and leave start blank, the algorithm will assume everything from the minimum number year in the data up to 2016. The same goes for the quarters and months fields as long as the start value is not greater than the end value.

This is much simpler if we use Excel Tables. I have converted the data you provided to an Excel Table called T_NatGas. I hope you know how Excel Table nomenclature works.

[I did not paste the entire data table again.]

MrExcel posts18.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Dates	Day	Month	Quarter	Year	Spot Peak PJM	Spot Peak NY-J			year	quarter	month
2	1/1/2010	1	1	1	2010	45.134	70.7781		Start	2010	1	2		correlation	0.739599
3	1/2/2010	2	1	1	2010	45.134	70.7781		End	2010	2	5
4	1/3/2010	3	1	1	2010	45.134	70.7781
5	1/4/2010	4	1	1	2010	102.4638	100.185
Sheet28

Cell Formulas
Range	Formula
O2	O2	=CORREL( IF((T_NatGas[Year]>=IF(ISBLANK(J2),MIN(T_NatGas[Year]),J2))*(T_NatGas[Year]<=IF(ISBLANK(J3),MAX(T_NatGas[Year]),J3))* (T_NatGas[Quarter]>=IF(ISBLANK(K2),MIN(T_NatGas[Quarter]),K2))*(T_NatGas[Quarter]<=IF(ISBLANK(K3),MAX(T_NatGas[Quarter]),K3))* (T_NatGas[Month]>=IF(ISBLANK(L2),MIN(T_NatGas[Month]),L2))*(T_NatGas[Month]<=IF(ISBLANK(L3),MAX(T_NatGas[Month]),L3)),(T_NatGas[Spot Peak PJM])), IF((T_NatGas[Year]>=IF(ISBLANK(J2),MIN(T_NatGas[Year]),J2))*(T_NatGas[Year]<=IF(ISBLANK(J3),MAX(T_NatGas[Year]),J3))* (T_NatGas[Quarter]>=IF(ISBLANK(K2),MIN(T_NatGas[Quarter]),K2))*(T_NatGas[Quarter]<=IF(ISBLANK(K3),MAX(T_NatGas[Quarter]),K3))* (T_NatGas[Month]>=IF(ISBLANK(L2),MIN(T_NatGas[Month]),L2))*(T_NatGas[Month]<=IF(ISBLANK(L3),MAX(T_NatGas[Month]),L3)),T_NatGas[Spot Peak NY-J]) )

 

[tjkramer]

Wow.... just... wow. That's an impressive piece of logic you created. Will give this a shot and see if I can make it work. Many thanks for the prompt replies and great help.

 

[tjkramer]

OK...I just made this WORK!! You are a genius. You absolutely saved me.
I can't thank you loud enough or long enough.
Prompt replies + tons of your valuable time + an answer that works first time I tried it = Amazing.
Thank you, thank you, thank you!!!

 

[DRSteele]

Oh heavens, I'm blushing.

You're welcome. I'm glad to have helped. Come back anytime.