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.)