I have 2 columns of numbers 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.
<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;">{=IFERROR(CORREL(IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";B4:B33)));IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";D4:D33)));"0")}</code>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 synchronization 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 <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; background-position: initial initial; background-repeat: initial initial;">vlookup</code>, 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>
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.
However in the columns A and C , there are dates that the counts were taken on.
<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;">{=IFERROR(CORREL(IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";B4:B33)));IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";D4:D33)));"0")}</code>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 synchronization 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 <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; background-position: initial initial; background-repeat: initial initial;">vlookup</code>, 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>
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.
