Running CORREL on misaligned data streams

ExcelNovice101

New Member
Joined
Sep 12, 2006
Messages
4
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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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:

Code:
=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

Code:
=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
ABCDEFGHIJK
1AB
21/31/200628.20%6/30/200527.60%Correlation0.50344
32/28/200655.30%7/31/20050.00%
43/31/200654.60%8/31/200597.10%Confirmation0.50344
54/30/200642.80%9/30/20050.00%
610/31/20057.30%
711/30/20050.00%
812/31/200527.10%
91/31/20060.00%
102/28/200646.20%
113/31/20060.00%
124/30/200618.90%
13
14
Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,726
Members
410,630
Latest member
JFORTH97
Top