How to calculate Pearson correlation coefficient with 2-property arrays?

Question

I have the following data:        A         B           C
1               Group 1     Group 2
2   Property 1     56         651
3   Property 2     97       1,380According to the https://www.mathsisfun.com/data/chi-square-calculator.html at Math Is Fun, these data have a Pearson correlation coefficient ("p") of 0.2463. But when I use Excel's function pearson() on them:=pearson(B2:B3, C2:C3)I get the result 1. I found an explanation of why this is happening in a https://stackoverflow.com/a/16064573/5803910 about a calculation in Python. But that answer seems to be saying that there is no way to get a Pearson of other than 1 with just two property values. But that doesn't seem to be correct, since the calculator linked above is doing it. So it may be that for some reason the function pearson() can't do it, but is there another way in Excel, perhaps using VBA?

sergeibaklan · Answer

anMSuser&nbsp;
But I found no one word on Chi-Square that p-value is Pearson correlation calculator. Correlation on this site is calculated here&nbsp;https://www.mathsisfun.com/data/correlation-calculator.html, explanation of it is at&nbsp;https://www.mathsisfun.com/data/correlation-calculator.html

anmsuser · Answer

SergeiBaklan&nbsp;The two links in your reply are the same. What was the second one supposed to be?&nbsp;And it appears that I was mistaken that the calculator I was using was doing the Pearson coefficient. But it is doing some other kind of correlation coefficient that works on 2-property arrays, whereas apparently Pearson is always 1 or -1 for 2-property arrays. So my question becomes, is there a way to calculate that other correlation coefficient in Excel?

savia · Answer

anMSuser&nbsp;I get the same value for the chi-sq using the proper methodology and function, attached.

sergeibaklan · Answer

anMSuser&nbsp;
Sorry, the second link is&nbsp;https://www.mathsisfun.com/data/correlation.html, it just explains the formula.
&nbsp;
I'm not a specialists in stats. You may check available functions here&nbsp;https://www.excelfunctions.net/excel-statistical-functions.html&nbsp;and here&nbsp;https://support.office.com/en-us/article/statistical-functions-reference-624dac86-a375-4435-bc25-76d659719ffd&nbsp;if something works with you.

anmsuser · Answer

Okay, I figured it out. SergeiBaklan was helpful in showing me that the calculation I need is not the same as the Pearson coefficient calculated by pearson(). Savia was helpful with his attached spreadsheet that showed that the calculation I need is performed by the function https://support.microsoft.com/en-us/office/chisq-test-function-2e8a7861-b14a-4985-aa93-fb88de3f260f. But that function is not helpful because it requires me to have a separate array of expected values.&nbsp;I had already written a VBA function that calculates chi-squared by the https://www.mathsisfun.com/data/chi-square-test.html. Now I've found that the calculation I need, the one done by the https://www.mathsisfun.com/data/chi-square-calculator.html, is performed by the Excel function https://support.office.com/en-us/article/chisq-dist-rt-function-dc4832e8-ed2b-49ae-8d7c-b28d5804c0f2. For the two arguments of that function, the first one is the output of my VBA function (using the method at the first link above) and the second argument is 1 (the degrees of freedom = (rows - 1) * (columns - 1) = 1).&nbsp;I have now confirmed that ChiSq.dist.RT() is producing the correct results. Thank you guys for leading me in a direction that allowed me to figure that out.

Forum Discussion

How to calculate Pearson correlation coefficient with 2-property arrays?

6 Replies

Resources