Running CORREL on misaligned data streams

[ExcelNovice101]

Hi everyone,

Would anyone have any suggestions on how to create a formula to compute correlations on a set of timeseries data where the dates don't necessarily start at the same point in time and are not aligned in Excel.

What I mean is:
A
1/31/2006 28.2%
2/28/2006 55.3%
3/31/2006 54.6%
4/30/2006 42.8%

B
6/30/2005 27.6%
7/31/2005
8/31/2005 97.1%
9/30/2005
10/31/2005 7.3%
11/30/2005
12/31/2005 27.1%
1/31/2006
2/28/2006 46.2%
3/31/2006
4/30/2006 18.9%



I need to compute correlation of points A to points B where only points that have corresponding dates appear in both A and B are included. In other words, only 4 points from each set are included in the correlation (all of A and the last 4 of B)

Thank you so much for your help!

 

[pgc01]

Hi ExcelNovice101

Hope this example can help you.

This example calculates the correlation between the elements in the first table and the corresponding elements in the second table. If an element in the first table does not exist in the second table, it is not considered.

In I2:

=CORREL(IF(COUNTIF(E2:E12,A2:A5),B2:B5),IF(COUNTIF(E2:E12,A2:A5),N(OFFSET(F2,-1+MATCH(A2:A5,E2:E12,0),0))))

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

I confirmed in I4 with

=CORREL(B2:B5,F9:F12)

For this last formula I replaced the empty cells with 0.

Hope this helps
PGC

P. S. Why are there empty values? Should you consider them in the calculation?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	A				B
2	1/31/2006	28.20%			6/30/2005	27.60%		Correlation	0.50344
3	2/28/2006	55.30%			7/31/2005	0.00%
4	3/31/2006	54.60%			8/31/2005	97.10%		Confirmation	0.50344
5	4/30/2006	42.80%			9/30/2005	0.00%
6					10/31/2005	7.30%
7					11/30/2005	0.00%
8					12/31/2005	27.10%
9					1/31/2006	0.00%
10					2/28/2006	46.20%
11					3/31/2006	0.00%
12					4/30/2006	18.90%
13
14
Sheet3

 

[ExcelNovice101]

This is great! Thanks so much