How to compare two variables with unequal data samples with CORREL function?

Question

Hi&nbsp;I have two variables denoted A and B.&nbsp;&nbsp;A has 5000 data points comprising data from the last 5000 days, whereas B only has 1000 data points comprising the last 1000 days.When I use CORREL function between all 5000 data points from A and 1000 data points from B, I get a correlation value of -0,711.However, when I only take the last 1000 data points from variable A (comprising the last 1000 days) and compare these with the 1000 data points from variable B by using CORREL function, I get a correlation value of 0,981.&nbsp;I don't understand how there can be such a big difference. Should I assume that the correlation value of 0,981 is most correct since there exists a data point for each of the 1000 data points from variable A to be compared to by variable B (and vice versa), or I am missing something here?&nbsp;Thanks for any help 😄

preagan · Answer

Martin349&nbsp;&nbsp;You are manipulating giving a misinterpretation of your data by using the arrays "'A'!E:E" and "'B'!E:E". While these arrays are the same size, the arrays actually have different amounts of data points. You should only use the range in which there are data points in the CORREL() function.

preagan · Answer

Hello&nbsp;Martin349,&nbsp;When using the CORREL() function, both arrays should have an equal amount of data points. CORREL([5000 data points], [1000 data points]) should return #N/A but you're saying it returns -0.711?

martin349 · Answer

HiPReagan&nbsp;Thanks for your response&nbsp;Yes, I use the following formula:=CORREL('A'!E:E;'B'!E:E)&nbsp;Where E:E in both cases represents the columns containing the data points.

martin349 · Answer

HiPReagan&nbsp;Thank you so much 🙂 That makes really good sense. Now I see the problem 🙂&nbsp;So I should maybe only take the past 1000 data points from variable A to compare the correlation coefficient with B?&nbsp;In other words, if I compare A to new variables (e.g. C, D, etc. ) and they have even less data points (let's assume C has 500 data points from past 500 days, and D only has 200 data points from past 200 days), then I should also only take the past 500 data points from A (when comparing to C), or 200 data points (when comparing to D)?I hope it makes sense ? 🙂Thanks again for the wonderful help.

preagan · Answer

Martin349&nbsp;&nbsp;I guess it depends on what relationship you're trying to measure. If you want to find the correlation between a subset of "A" and either "B", "C", or "D", then it really depends on what you're looking to compare. If you are simply looking to find how strong the correlation between "A" and "B" are, subject to the same dates, then use the last 1000 data points of "A". The same goes with "C" (last 500 data points) and "D" (last 200 data points). But again, it really depends on what you're looking to measure.

Forum Discussion

How to compare two variables with unequal data samples with CORREL function?

5 Replies

Resources