How do I correlate two long time series if the dates of the data don't match up?

Question

How do I correlate two long time series if the dates of the data don't match up?

Anonymous

I'm actually going to do this in a correlation matrix, but I figure I can scale it out once I manage to do just two of the series.

For example imagine this situation.

Dates Number 1 Dates Number 2

1 1 1 1

2 2 3 3

3 3 4 4

5 5 5 5

So this is the basic over generalized situation going on. I have two sets of data (actually more), but their dates don't line up, so in one specific time period, one set may have more data than the other set. How do I manage to separate the two so excel will only correlate based on dates contained in each set (esentially skipping over the unshared dates)? I'm going to do this into a corr. matrix, so insight on how to do that with more than just 2 differing sets is very welcome.

Thanks!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

3 additional answers

Answer 1

Maybe I could have been more clear in my first post.

I think VLOOKUP would work best.

If you have the following in cells A1:D5:

Dates	Number 1	Dates	Number 2
1	1	1	1
2	2	3	3
3	3	4	4
5	5	5	5

Then in F1:F6 you could place:

Dates
1
2
3
4
5

Then in G1: Number 1

In H1: Number 2

Then in G2 you can use the formula: =VLOOKUP($F2,$A$2:$B$5,2,FALSE)

and in H2 you can use the formula: =VLOOKUP($F2,$C$2:$D$5,2,FALSE)

Now you can drag cells G2:H2 down to row 6.

You should now see this:

Dates	Number 1	Number 2
1	1	1
2	2	#N/A
3	3	3
4	#N/A	4
5	5	5

The #N/A's are where data was missing. In Excel 2007+ we can use the IFERROR function to handle this if you want your table to visually look nicer. For example, in G2 you could use instead:

=IFERROR(VLOOKUP($F2,$A$2:$B$5,2,FALSE),"")

Now the cells with missing data will _appear_ blank.

Does that help?

Cheers

Rich

Answer 2

If you are working in multiple sets of data it might be easiest to create a single date set that encompases all of the possible dates so in your example...

Dates Number 1 Number 2

1 1 1

2 2

3 3 3

4 4

5 5 5

at that point it should be fairly easy to correlate any set of dates you want.

Yes this is fairly obvious, but I guess I should of clarified. With multiple large data sets, HOW do you get the data into that format so you can then correlate it.

Answer 3

If you are working in multiple sets of data it might be easiest to create a single date set that encompases all of the possible dates so in your example...

Dates	Number 1	Number 2
1	1	1
2	2
3	3	3
4	4
5	5	5

at that point it should be fairly easy to correlate any set of dates you want.

Answer 4

You could use the FORECAST function.

In your above example, set out a column with all the dates (1, 2, 3, 4, 5...) these will be your x values.

Use the entire column under "Number 1" in your sample data for the known y's, using absolute references (with $'s).

Then use the entire column of Dates in one set as known x's, again using absolute references (with $'s) (this would be the first column in your example.

Alternatively, you could use =IFERROR(VLOOKUP(new_date, absolute_ref_to_first_two_cols, 2, FALSE), "") so that the cell appears blank if no data for that new_date exists in your original data.

Hope that helps.

Cheers

Rich

Share via

How do I correlate two long time series if the dates of the data don't match up?

3 additional answers