View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.charting
Jerry W. Lewis Jerry W. Lewis is offline
external usenet poster
 
Posts: 837
Default how i calculate pearson correlation coefficient for excel grap

The numerical problems with the pre-2003 Pearson algorithm are the same as
with the pre-2003 StDev, Rsq, Slope, etc. They have been discussed in the
statistical literature for over 40 years and in these newsgroups for over 10.
You might find
http://groups.google.com/group/micro...a03470e7a1c650
to be useful.

You will find a number of threads where people report Rsq<0 or Abs(Rsq)1.
In those instances Pearson will show much worse numerical problems than your
simple tests have shown.

For a relatively simple example, put =$C$1 in A1:B1 and =$C$1+1 in A2:B2.
=CORREL(A1:A2,B1:B2) and =PEARSON(A1:A2,B1:B2) should both be 1 for any
numeric value in C1. However if C1 contains 1E8 then PEARSON will give
#DIV/0, and if C1 contains 1E12 then PEARSON will give -1.

Jerry

"James Silverton" wrote:


"Jerry W. Lewis" wrote in message
...
Correl is the better choice. In Excel 2003 and later Correl
and Pearson are
identical. Prior to 2003, they are mathematically equivalent,
but Peason is
numerically inferior.


Without disputing your analysis, since all my tests seem to
produce identical results with Excel 2002, can you point me to
details of why PEARSON is inferior? Perhaps, you might let me
have some data that would produce different answers.



--
James Silverton
Potomac, Maryland