CORREL function question

Anonymous
2022-12-17T11:06:16+00:00

Hi,

I am doing some correlations on data. I have 69 rows of two columns. 

I have 3 groups (subsets) of data (28 rows, 29 rows and 12 rows). When I use CORREL on the two columns for each of these subsets I get the following results: 

0.202
0.187
0.130

But when I use the CORREL function on all 69 rows I get a result of 0.002.

Does this make sense? Can anyone explain it to me?

Thanks!!!

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.7K Reputation points MVP Volunteer Moderator
2022-12-17T12:09:06+00:00

It all depends on the data, and it probably makes perfect sense,

Take a look at this very simple example:

Image

The data in the orange range consists of only 2 points, so =CORREL(A2:A3,B2:B3) returns 1.

The same goes for the data in the blue range and in the gray range. Both =CORREL(A4:A5,B4:B5) and =CORREL(A6:A7,B6:B7) return 1.

But if you look at the entire range, there is no strong correlation between X and Y:

Image

=CORREL(A2:A7,B2:B7) returns just 0.213

Was this answer helpful?

2 people found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-18T14:44:58+00:00

    Hi Gadzooks!

    You are very much welcome.

    Kind Regards, Shakiru

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-12-18T11:53:10+00:00

    Amazing. Got it. Thanks!!!!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-12-18T10:55:25+00:00

    Hi,

    I am doing some correlations on data. I have 69 rows of two columns. 

    I have 3 groups (subsets) of data (28 rows, 29 rows and 12 rows). When I use CORREL on the two columns for each of these subsets I get the following results: 

    0.202
    0.187
    0.130

    But when I use the CORREL function on all 69 rows I get a result of 0.002.

    Does this make sense? Can anyone explain it to me?

    Thanks!!!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-12-17T12:09:57+00:00

    Hi Gadzooks!

    Thank you for writing to the Microsoft Answer Community Forum. I am Shakiru, a user like you, and I am glad to be helping you out today.

    Yes, it makes sense, if you calculate the correlation for different distributions you arrive at different Answers.

    This is because correlation refers to the statistical relationship between the two entities over a certain range of occurrences, once the data range changes (increases or decreases), the correlation also differs.

    Therefore, what you obtain is correct once the formula is correctly applied

    Check here for detail: https://support.microsoft.com/office/995dcef7-0c0a-4bed-a3fb-239d7b68ca92

    Kind Regards, Shakiru

    Please, remember to give back to the community. Help the next person with this problem by indicating if this conversation was helpful. Thank you

    Was this answer helpful?

    0 comments