If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 how i calculate pearson correlation coefficient for excel graph
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## how i calculate pearson correlation coefficient for excel graph

 Thread Tools Display Modes
#1
September 16th 06, 02:29 AM posted to microsoft.public.excel.charting
 university student that is lost external usenet poster Posts: 1
how i calculate pearson correlation coefficient for excel graph

how do I calculate the pearson correlation coefficient for on microsoft excel
for a grapg of information. If it is not possible is there another program
that can do this for me?
 Ads
#2
September 16th 06, 02:59 AM posted to microsoft.public.excel.charting
 Jon Peltier external usenet poster Posts: 6,582
how i calculate pearson correlation coefficient for excel graph

In a cell in the worksheet, enter this formula

=CORREL({y range},{x range})

where {x range} and {y range} are the ranges occupied by the X and Y values
in the sheet. You can select them by clicking and dragging while you are
entering the formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"university student that is lost" <university student that is
> wrote in message
news
> how do I calculate the pearson correlation coefficient for on microsoft
> excel
> for a grapg of information. If it is not possible is there another program
> that can do this for me?

#3
September 16th 06, 01:07 PM posted to microsoft.public.excel.charting
 Del Cotter external usenet poster Posts: 560
how i calculate pearson correlation coefficient for excel graph

On Fri, 15 Sep 2006, in microsoft.public.excel.charting,
Jon Peltier > said:

>In a cell in the worksheet, enter this formula
>
>=CORREL({y range},{x range})

Shouldn't that be =PEARSON({y range},{x range})? I only have Excel 97,
but I'm surprised to see that PEARSON() and CORREL() do not have a
pointer to each other in the "See also" section of the help file.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.
#4
September 17th 06, 05:28 AM posted to microsoft.public.excel.charting
 Jon Peltier external usenet poster Posts: 6,582
how i calculate pearson correlation coefficient for excel graph

A quick glimpse at Google led me to believe they were one and the same. Just
now Wikipedia tells me:

"The CORREL() function in many major spreadsheet packages, such as Microsoft
Excel and Gnumeric calculates Pearsons correlation coefficient."

Wolfram adds that:

"The correlation coefficient is also known as the product-moment coefficient
of correlation or Pearson's correlation."

I wouldn't count on the Excel help files to be much help, especially about
statistics.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"Del Cotter" > wrote in message
...
> On Fri, 15 Sep 2006, in microsoft.public.excel.charting,
> Jon Peltier > said:
>
>>In a cell in the worksheet, enter this formula
>>
>>=CORREL({y range},{x range})

>
> Shouldn't that be =PEARSON({y range},{x range})? I only have Excel 97,
> but I'm surprised to see that PEARSON() and CORREL() do not have a pointer
> to each other in the "See also" section of the help file.
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> ,
> which goes to a spam folder-- please send your email to del3 instead.

#5
September 17th 06, 01:21 PM posted to microsoft.public.excel.charting
 Del Cotter external usenet poster Posts: 560
how i calculate pearson correlation coefficient for excel graph

You're right, a quick test of the two functions suggests they give the
same numeric result every time.

Jon Peltier > said:
>A quick glimpse at Google led me to believe they were one and the same. Just
>now Wikipedia tells me:
>
>"The CORREL() function in many major spreadsheet packages, such as Microsoft
>Excel and Gnumeric calculates Pearsons correlation coefficient."

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.
#6
September 17th 06, 01:38 PM posted to microsoft.public.excel.charting
 James Silverton external usenet poster Posts: 182
how i calculate pearson correlation coefficient for excel graph

Hello, Jon!
You wrote on Sun, 17 Sep 2006 00:28:59 -0400:

JP> "The CORREL() function in many major spreadsheet packages,
JP> such as Microsoft Excel and Gnumeric calculates Pearsons
JP> correlation coefficient."

JP> Wolfram adds that:

JP> "The correlation coefficient is also known as the
JP> product-moment coefficient of correlation or Pearson's
JP> correlation."

JP> I wouldn't count on the Excel help files to be much help,
JP> especially about statistics.

It is interesting that the Help entries for PEARSON and CORREL
look confusingly different. Rather than analyse the equations, I
calculated PEARSON and CORREL for several sets of data with
identical numerical results :-)

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

#7
September 18th 06, 02:44 AM posted to microsoft.public.excel.charting
 Jerry W. Lewis external usenet poster Posts: 837
how i calculate pearson correlation coefficient for excel grap

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.

Jerry

"Del Cotter" wrote:

> On Fri, 15 Sep 2006, in microsoft.public.excel.charting,
> Jon Peltier > said:
>
> >In a cell in the worksheet, enter this formula
> >
> >=CORREL({y range},{x range})

>
> Shouldn't that be =PEARSON({y range},{x range})? I only have Excel 97,
> but I'm surprised to see that PEARSON() and CORREL() do not have a
> pointer to each other in the "See also" section of the help file.
>
> --
> Del Cotter
> NB Personal replies to this post will send email to ,
> which goes to a spam folder-- please send your email to del3 instead.

#8
September 18th 06, 04:52 AM posted to microsoft.public.excel.charting
 James Silverton external usenet poster Posts: 182
how i calculate pearson correlation coefficient for excel grap

"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

#9
September 18th 06, 05:09 AM posted to microsoft.public.excel.charting
 James Silverton external usenet poster Posts: 182
how i calculate pearson correlation coefficient for excel grap

Hello, James!
You wrote on Sun, 17 Sep 2006 23:52:37 -0400:

JS> "Jerry W. Lewis" > wrote in
JS> 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.
??>>
JS> Without disputing your analysis, since all my tests seem to
JS> produce identical results with Excel 2002, can you point me
JS> to details of why PEARSON is inferior? Perhaps, you might
JS> let me have some data that would produce different answers.

I'll just add that I checked my results again but I had to look
at the 14th or 15th decimal to get a numerical difference. I
suppose there must be data where the difference is more apparent
but I'd debate whether the differences of the coefficients have
any meaning for my data.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

#10
September 18th 06, 12:16 PM posted to microsoft.public.excel.charting
 Jerry W. Lewis external usenet poster Posts: 837
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
>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 08:02 PM Hyperlinkage of one graph with another graph or Drill down graph Sanjay Kumar Singh Charts and Charting in Excel 1 January 3rd 06 12:22 PM How do I calculate the area under a curve in an Excel graph? Suzanne Graham New Users to Excel 2 July 7th 05 01:51 PM spearman correlation coefficient Marcel LabontÃ© Excel Discussion (Misc queries) 1 May 12th 05 04:00 AM Correlation Coefficient Issue Diane Excel Worksheet Functions 8 December 17th 04 03:48 AM

All times are GMT +1. The time now is 01:33 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2015, Jelsoft Enterprises Ltd.
Copyright ©2004-2015 ExcelBanter.
The comments are property of their posters.