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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

pgc01

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

Forum statistics

Threads
1,140,941
Messages
5,703,299
Members
421,289
Latest member
java

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top