# Correlation Coefficient Formula

• January 3rd 2006, 03:45 AM
jimbot
Correlation Coefficient Formula
Hi,

I have been trying to calculate the correlation coefficient using an excel formula (rather than the built in function). I'm not sure if I'm doing it right because I put in some data with a perfect direct correlation (to check) it came out 1 using the built in but .953923 using my formula. Here's how I went about it (with apologies for the way I write the formulae - it's kind of quasi Excel syntax, I hope that mathematicians can understand it:

r= Sxy / SqrRt(Sxx.Syy)

=(((SumX) (SumY))/n) / (SumX^2 - (SumX^2/n)) (SumY^2 - (SumY^2/n)))

I checked the formula itself and it does just what I think it should - ie if it's the correct formula for calculating r then it should be good. So, since it's not calculating r (at least the answer isn't 1 - which I expect) then it must be that I'm mistaken in expanding the formula as I have. The other possibility is that at some point Excel is rounding a calculation...but from .953923 to 1 is a big gap!

Your my last hope! Could somebody look and see if my formula for calculating r is correct?

(Please note this is emphatically NOT homework. I'm trying to prepare for a specialist training course I'm taking on Using Excel for Statistics.)

Jim
• January 3rd 2006, 06:50 AM
CaptainBlack
Quote:

Originally Posted by jimbot
Hi,

I have been trying to calculate the correlation coefficient using an excel formula (rather than the built in function). I'm not sure if I'm doing it right because I put in some data with a perfect direct correlation (to check) it came out 1 using the built in but .953923 using my formula. Here's how I went about it (with apologies for the way I write the formulae - it's kind of quasi Excel syntax, I hope that mathematicians can understand it:

r= Sxy / SqrRt(Sxx.Syy)

=(((SumX) (SumY))/n) / (SumX^2 - (SumX^2/n)) (SumY^2 - (SumY^2/n)))

I checked the formula itself and it does just what I think it should

Using a slight modification of your notation this should be:

$r=\frac{Sum(XY)-Sum(X)Sum(Y)/n}{[Sum(X^2)-(Sum(X))^2/n][Sum(Y^2)-(Sum(Y))^2/n]}$

(see:http://mathworld.wolfram.com/Correla...efficient.html)

RonL
• January 3rd 2006, 08:12 AM
jimbot
Quote:

Originally Posted by CaptainBlack
Using a slight modification of your notation this should be:

$r=\frac{Sum(XY)-Sum(X)Sum(Y)/n}{[Sum(X^2)-(Sum(X))^2/n][Sum(Y^2)-(Sum(Y))^2/n]}$

(see:http://mathworld.wolfram.com/Correla...efficient.html)

RonL

Thank you!