Thread: 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?

Thanks in advance

(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

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:



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

RonL

Originally Posted by CaptainBlack

Using a slight modification of your notation this should be:



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

RonL

Thank you!