Correlation Coefficient with data that has missing info

Hi there,

I have 2 sets of data, im trying to find the correlation coefficient of both sets of data. However, in the set of data there are some missing blanks like below

Dates 1 Price 1 Dates 2 Price 2
3/18/1996 10 3/18/1996 13.5
3/19/1996 10.125 3/19/1996 14
3/20/1996 10.125 #N/A #N/A
3/22/1996 10.25 3/22/1996 14
3/25/1996 10.25 3/25/1996 14.0625
#N/A #N/A 3/26/1996 13.25
3/27/1996 10.25 3/27/1996 13.1875
3/28/1996 10.25 3/28/1996 13.125
3/29/1996 10.25 3/29/1996 13.5


How do i find the correlation coefficient without having to add the missing data inside?

Thanks alot guys!

Anyone help please?

Originally Posted by shg

Andrian,

One approach is a UDF that filters both data series to remove the values that are #N/A in either. Would that work for you?

You mean the cells that have N/A are filtered out and it becomes empty/blank?

Well i tried this, for example on 3/20/1996, Set 1 have data, but Set 2 is N/A.
So i deleted the values of Set 2 on 3/20/1996 so that the cell is not empty. However, the CORREL function still includes the 3/20/1996 data as part of the calculation which obviously is wrong.


Of course i could very well delete the whole entry on 3/20/1996 and manually not include the data so that the calculation is correct, but this is a very small sample. And i'm working on a very big sample with many data series which is why i'm waiting to see if anyone here has the answer??