CODEWITHSUNDEEP
excelexcel-formula
LMunyan
LMunyan

Reputation: 116

Adjusting correlations based on an date range reference cell

I have set up a workbook that has three main columns. Column A holds dates at a weekly level. Column B has the data for one variable, and column C has data for the second variable. 

 1/16/2012   3,317   5,258,000 
 1/23/2012   3,535   8,965,000 
 1/30/2012   3,663   9,527,000 
 2/6/2012    3,629   9,127,000

I am then calculating correlations based on these two variables. I am interested, however, in a way to automatically adjust the date range the correlations are being calculated for based on two reference cells - one with a start date and one with an end date. I think this would require nesting functions inside the correl() formula.

My initial attempt at solving the problem looks like this:

=correl(CELL("address",INDEX(A8:C295,MATCH(P2,$A$8:$C$295,0),4)):CELL("address",INDEX(A8:C295,MATCH(P,$A$8:$C$295,0),4)), CELL("address",INDEX(A8:C295,MATCH(P2,$A$8:$C$295,0),5)):CELL("address",INDEX(A8:C295,MATCH(P3,$A$8:$C$295,0),5)))

Cell P2 is the date I want the correlations to start at. Cell P3 is the end date. The current solution does not seem to be working because the CELL function is returning the cell value as a text variable. Does anyone know how to supplement this or a different method for achieving my objectives?

Again, I just want to be able to adjust what time frame the correlations are running for example, the current date range is 12/26/2011-6/26/17. I may want to look only at the correlation for part of 2016 so I would want to adjust the start and end dates in the reference cells (P2 and P3 in the formula above) so that the correlation is only calculated for the specified time range. It would then be easy to evaluate correlations for many time ranges as only the reference cells would need updating.

Upvotes: 0

Views: 675

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Get rid of the Cell("Address", part:

=CORREL(INDEX(B:B,MATCH(P2,A:A,0)):INDEX(B:B,MATCH(P3,A:A,0)),INDEX(C:C,MATCH(P2,A:A,0)):INDEX(C:C,MATCH(P3,A:A,0)))

Now the ,0 in the third criterion expects an exact match. If your data is sorted on column A then you may want ,1 instead to find where it fits and not an exact match.

Upvotes: 1

Related Questions