StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCorrelation function for matching dates within a range of data
primarykey
Id
14231841
data
AcceptedAnswerId
14242928
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-01-09T09:19:58.413
FavoriteCount
0
LastActivityDate
2013-01-19T02:28:49.140
LastEditDate
2013-01-09T16:29:42.710
LastEditorUserId
128421
OwnerUserId
1862778
ParentId
0
PostTypeId
1
Score
0
ViewCount
2357
LastEditorDisplayName
text
Body
I have a spreadsheet with four columns where columns B and D contain counts 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 following correlation formula ignores them: <pre><code>{=IFERROR(CORREL(IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";B4:B33));IF(B4:B33<>"Bad";IF(D4:D33<>"Bad";D4:D33)));"0")} </code></pre> Columns A and C contain the dates that the counts were taken on. Unfortunately, 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 dates out of synchronization and the correlation falls apart. Is there any way I can adapt the formula for it to check to see if the dates are the same before correlating? It'd almost be like a <code>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. For example, for the last six days performed on the 28th of December, looking at six calendar days up to but not including the 28th: <pre><code> A B C D 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 </code></pre> The last cells of columns C and D are blank. I would like the formula to only correlate the four days, 23rd, 24th, 26th and 27th, matching up the appropriate counts. 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.
Tags
<excel><function>
Title
Correlation function for matching dates within a range of data
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USthe Tin Man
UserOwnerUserId
1. USCylen
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POCorrelation function for matching dates within a range of data
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTApproveEditSuggestion
2. VO
 singulars
 PostPostId
 POCorrelation function for matching dates within a range of data
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTApproveEditSuggestion
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.