Correlation using lookups

[Woofy_McWoof_Woof]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A - Month
[/TD]
[TD]B - Date
[/TD]
[TD]C - Period
[/TD]
[TD]D - Price A
[/TD]
[TD]E -Price b
[/TD]
[/TR]
[TR]
[TD]=date(year(B2),month(B2),1)
[/TD]
[TD]01/01/2010
[/TD]
[TD]1
[/TD]
[TD]32.22
[/TD]
[TD]33.01
[/TD]
[/TR]
[TR]
[TD]=date(year(B3),month(B3),1)
[/TD]
[TD]01/01/2010
[/TD]
[TD]2
[/TD]
[TD]30.72
[/TD]
[TD]31.78
[/TD]
[/TR]
[TR]
[TD]=date(year(B4),month(B4),1)
[/TD]
[TD]01/01/2010
[/TD]
[TD]3
[/TD]
[TD]30.91
[/TD]
[TD]30.65
[/TD]
[/TR]
[TR]
[TD]=date(year(B5),month(B5),1)
[/TD]
[TD]01/01/210
[/TD]
[TD]4
[/TD]
[TD]25.91
[/TD]
[TD]29.32
[/TD]
[/TR]
</tbody>[/TABLE]
Hi

I'm attempting to prove if there is a correlation between two sets of prices from January 2010 - August 2018. For each day there are 48 period, so for January 2010 there are 1,488 rows of data to look at. In order to simplify things I have set the correlation to look at all the reference cells in January 2010 which means the reference is (simplistically) =CORREL(D2:D1489,E2:E1489). This is fine as it gives me the result I need, however, it is a manually process which means if I have to redo the formula for each month up to August 2018 it will take forever.
Is there a way for the correlation formula to look up the month required and to then calculate the result after looking at all applicable cells in that month? Ideally I would then just drag the formula own for all the remaining months so for instance Jan 2010 = X, Feb 2010 = y etc.

Thank for your help. If you need any more info then please let me know.

Woof

 

[DRSteele]

You can just create a set of arrays within function CORREL to filter for the dates. Like this, where I used only two days of Jan 2010 to create my sample data:

Book1
	G	H	I
1	Month	Year	Correlation A|B
2	1	2010	-0.099841564
3	2	2010
Sheet48

Cell Formulas
Range	Formula
I2	=CORREL((YEAR($B$2:$B$97)=H2)*(MONTH($B$2:$B$97)=G2)*$D$2:$D$97,(YEAR($B$2:$B$97)=H2)*(MONTH($B$2:$B$97)=G2)*$E$2:$E$97)

_{^{<strike>
</strike>}}

 

[Woofy_McWoof_Woof]

Thanks a lot, it worked