Results 1 to 3 of 3

Math Help - Correlation Coefficient Formula

  1. #1
    Newbie
    Joined
    Jan 2006
    Posts
    3

    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
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Grand Panjandrum
    Joined
    Nov 2005
    From
    someplace
    Posts
    14,972
    Thanks
    4
    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
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Jan 2006
    Posts
    3
    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!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Replies: 0
    Last Post: February 25th 2011, 01:15 AM
  2. Replies: 1
    Last Post: December 11th 2010, 11:16 PM
  3. Correlation coefficient?
    Posted in the Statistics Forum
    Replies: 4
    Last Post: June 28th 2010, 10:13 PM
  4. Correlation coefficient.
    Posted in the Statistics Forum
    Replies: 3
    Last Post: June 1st 2010, 05:02 PM
  5. Correlation coefficient
    Posted in the Advanced Statistics Forum
    Replies: 2
    Last Post: October 10th 2009, 10:23 AM

Search Tags


/mathhelpforum @mathhelpforum