Correlation using lookups

[Woofy_McWoof_Woof]

A - Month	B - Date	C - Period	D - Price A	E -Price b
=date(year(B2),month(B2),1)	01/01/2010	1	32.22	33.01
=date(year(B3),month(B3),1)	01/01/2010	2	30.72	31.78
=date(year(B4),month(B4),1)	01/01/2010	3	30.91	30.65
=date(year(B5),month(B5),1)	01/01/210	4	25.91	29.32

<tbody>
</tbody>

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