Woofy_McWoof_Woof
Board Regular
- Joined
- Oct 7, 2016
- Messages
- 60
- Office Version
- 365
- Platform
- Windows
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>
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