Forum Discussion

Copper Contributor

Feb 28, 2020

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

Hi I have two variables denoted A and B. 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 CORR...

Formulas and Functions

Bronze Contributor

Feb 28, 2020

Hello Martin349,

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
Copper Contributor
Feb 28, 2020
HiPReagan
Thanks for your response

Yes, I use the following formula:
=CORREL('A'!E:E;'B'!E:E)

Where E:E in both cases represents the columns containing the data points.
- PReagan
  Bronze Contributor
  Feb 28, 2020
  Martin349
  
  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.
  - Martin349
    Copper Contributor
    Feb 29, 2020
    HiPReagan
    Thank you so much 🙂 That makes really good sense. Now I see the problem 🙂
    
    So I should maybe only take the past 1000 data points from variable A to compare the correlation coefficient with B?
    
    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.

Resources