# Running CORREL on misaligned data streams

#### ExcelNovice101

##### New Member
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!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi ExcelNovice101

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

This is great! Thanks so much

Replies
4
Views
496
Replies
2
Views
277
Replies
1
Views
1K
Replies
3
Views
337
Replies
9
Views
496

1,219,812
Messages
6,150,367
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back