Correlation function for matching dates within a range of data

[trazer985]

i have 2 columns of number of cars using various roads, taken in the last 30 days. A macro has processed the data in these ranges and changed the outlier values to "Bad" so the formula ignores them.
However in the columns A and C , there are dates that the counts were taken on.
{=IFERROR(CORREL(IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";B4:B33)));IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";D4:D33)));"0")}
sometimes these dates aren't the same (no data available on christmas in some areas, boxing day in others etc. Obviously this destroys the correlation, as it puts the days out of synchronisation and the correlation falls apart.
Is there any way i can adapt the formula for it to check the dates are the same before correlating? It'd almost be like a vlookup, find the date, if match, add to calculation, if not then go to the next date. i'm happy for it to only correlate 26 of the 30 days if there are that many conflicts.
example for last 6 days performed on the 28th of december (looking for 6 calendar days up to but not including 28th dec.

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;"> 22/12/2012 9 22/12/2012 Bad 23/12/2012 10 23/12/2012 22 24/12/2012 3 24/12/2012 21 25/12/2012 7 26/12/2012 18 26/12/2012 8 27/12/2012 15 27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.</code></pre>it could only correlate the 3 days of 23, 24 and 27.
Given that i'm going to be correlating this information with a number of locations, each with different reporting dates, filtering the dates before correlating and deleting unmatched dates is not a great option, but I will consider it if there is no other way.
Hope the question is clear.
Thanks in advance,

Regards
T

 

[trazer985]

22/12/2012 9 22/12/2012 Bad
23/12/2012 10 23/12/2012 22
24/12/2012 3 24/12/2012 21
25/12/2012 7 26/12/2012 18
26/12/2012 8 27/12/2012 15
27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.

fixed this for the data set, had several issues trying to post to forums, had a posting failed message every time, didnt realise it had posted.

 

[Aladin Akyurek]

i have 2 columns of number of cars using various roads, taken in the last 30 days. A macro has processed the data in these ranges and changed the outlier values to "Bad" so the formula ignores them.
However in the columns A and C , there are dates that the counts were taken on.
{=IFERROR(CORREL(IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";B4:B33)));IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";D4:D33)));"0")}
sometimes these dates aren't the same (no data available on christmas in some areas, boxing day in others etc. Obviously this destroys the correlation, as it puts the days out of synchronisation and the correlation falls apart.
Is there any way i can adapt the formula for it to check the dates are the same before correlating? It'd almost be like a vlookup, find the date, if match, add to calculation, if not then go to the next date. i'm happy for it to only correlate 26 of the 30 days if there are that many conflicts.
example for last 6 days performed on the 28th of december (looking for 6 calendar days up to but not including 28th dec.

<code style='margin: 0px; padding: 0px; border: 0px currentColor; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, serif; font-size: 14px; vertical-align: baseline;'> 22/12/2012 9 22/12/2012 Bad 23/12/2012 10 23/12/2012 22 24/12/2012 3 24/12/2012 21 25/12/2012 7 26/12/2012 18 26/12/2012 8 27/12/2012 15 27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.</code>it could only correlate the 3 days of 23, 24 and 27.
Given that i'm going to be correlating this information with a number of locations, each with different reporting dates, filtering the dates before correlating and deleting unmatched dates is not a great option, but I will consider it if there is no other way.
Hope the question is clear.
Thanks in advance,

Regards
T

22/12/2012 9 22/12/2012 Bad
23/12/2012 10 23/12/2012 22
24/12/2012 3 24/12/2012 21
25/12/2012 7 26/12/2012 18
26/12/2012 8 27/12/2012 15
27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.

fixed this for the data set, had several issues trying to post to forums, had a posting failed message every time, didnt realise it had posted.

I think you meant to say that the values corresponding 23, 24, 26, and 27 must be correlated, right?

 

[trazer985]

I think you meant to say that the values corresponding 23, 24, 26, and 27 must be correlated, right?

yes, sorry. the values for those dates. Is what im asking for possible? If it's not i'll have to rework the whole thing, which is gonna suck real hard.

 

[Aladin Akyurek]

yes, sorry. the values for those dates. Is what im asking for possible? If it's not i'll have to rework the whole thing, which is gonna suck real hard.

Date X	X	Date Y	Y		4
22-Dec-12	9	22-Dec-12	Bad		-0.67916
23-Dec-12	10	23-Dec-12	22
24-Dec-12	3	24-Dec-12	21
25-Dec-12	7	26-Dec-12	18
26-Dec-12	8	27-Dec-12	15
27-Dec-12	15

<colgroup><col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3015;" width="85"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <col style="width: 48pt;" span="3" width="64"> <tbody>
</tbody>

F1, control+shift+enter, not just enter:

=COUNT(IF(ISNUMBER(MATCH(A2:A7,IF(ISNUMBER(D2:D7),C2:C7),0)),
  IF(ISNUMBER(B2:B7),B2:B7)))

F2, control+shift+enter:

=CORREL(
  N(OFFSET(B2:B7,
   SMALL(IF(ISNUMBER(MATCH(A2:A7,IF(ISNUMBER(D2:D7),C2:C7),0)),
    IF(ISNUMBER(B2:B7),ROW(B2:B7)-ROW(B2))),ROW(INDIRECT("1:"&F1))),0,1)),
  N(OFFSET(D2:D7,
   SMALL(IF(ISNUMBER(MATCH(C2:C7,IF(ISNUMBER(B2:B7),A2:A7),0)),
    IF(ISNUMBER(D2:D7),ROW(D2:D7)-ROW(D2))),ROW(INDIRECT("1:"&F1))),0,1)))

 

[trazer985]

testing this out, thank you very much. my actual ranges are referenced using an indirect, which is a positively brainfrying ordeal to try and fix for.

 

[trazer985]

testing this out, thank you very much. my actual ranges are referenced using an indirect, which is a positively brainfrying ordeal to try and fix for.

It seems to work, I have NO idea how you came up with building that formula. Next time you're in Amsterdam I'll buy you a drink.

 

[Aladin Akyurek]

It seems to work, I have NO idea how you came up with building that formula.

That's great. Not sure whether a re-construction would be satisfactory. Anyway, the essential ingredient is to feed OFFSET with relevant numbers --> corresponding rows. Calculating relevant numbers was the hard part...

Next time you're in Amsterdam I'll buy you a drink.

I accept. Are you living in Amsterdam?

 

[trazer985]

That's great. Not sure whether a re-construction would be satisfactory. Anyway, the essential ingredient is to feed OFFSET with relevant numbers --> corresponding rows. Calculating relevant numbers was the hard part...



I accept. Are you living in Amsterdam?

I am indeed. Live in the outskirts, work near Dam Square

 

[Aladin Akyurek]

I am indeed. Live in the outskirts, work near Dam Square

I let you know next time I'm to Amsterdam. You are also welcome to visit my school in Den Haag.

Regards,

Aladin