three different results for same data set (r^2)

[iknowu99]

X	Y
0.990	2.005
0.958	2.000
0.948	2.000
0.938	2.000
0.907	2.000
0.894	2.000
0.884	2.000
0.874	2.000
0.857	2.000
0.853	2.000
0.838	2.000
0.833	2.000
0.821	2.000
0.573	2.000
0.454	2.000
0.231	2.000
0.199	2.000
0.177	2.000
0.175	2.000
0.148	2.000
0.145	2.000
0.143	2.000
0.133	2.000
0.106	1.995
0.094	1.995
0.089	1.995
0.088	1.995
0.081	1.995
0.079	1.995
0.078	1.995
0.069	1.995
0.056	1.995
0.037	1.995
0.017	1.995
0.013	1.995
0.012	1.995
0.005	1.995
0.003	1.995
0.002	1.995
0.000	1.995

1. running formula function =rsq(y,x)= 0.556231039
2. run chart and trend, then display rsq value on chart gets .7476
3. running data analysis, regression, gets R Square 0.526387388

 

[bolo]

Which version of excel is it?
in Excel 2007, the rsq value in charting is 0.5562.

Regards

Bolo

 

[iknowu99]

yea i'm using excel 2007

that's what i got first time...try running a graph with a trend line, then check mark the box that says show r squared.....


r^2 don't match up...don't know which one to trust?!

 

[bolo]

Hi I have tried all three methods.

rsq formula = 0.556159
Rsquared trendline on xy scatterchart = 0.5562
rsq using analysis toolpack 0.556159

So for me it seems ok.

0.7476 looks suspiciously close to the Multiple R value. Not wishing to be pedantic about it all but are you sure you are looking at the right value?

If so i guess the other thing to check is if this was a previous problem corrected in the service packs.

I am running excel 2007 SP2, if it helps.

Regards

Bolo

 

[tusharm]

A *very, very* good first step is to always plot the data.

If you do that with the provided sample, you'll see that it looks like a step function -- a step function with no variability in the result.

That makes it hard for any other kind of function to "fit" the data.

Also, RSQ gives the square of the Pearson product moment correlation coefficient. AFAIK, and I could be wrong, it is not the same as the R-squared from a regression.

X	Y
0.990	2.005
0.958	2.000
0.948	2.000
0.938	2.000
0.907	2.000
0.894	2.000
0.884	2.000
0.874	2.000
0.857	2.000
0.853	2.000
0.838	2.000
0.833	2.000
0.821	2.000
0.573	2.000
0.454	2.000
0.231	2.000
0.199	2.000
0.177	2.000
0.175	2.000
0.148	2.000
0.145	2.000
0.143	2.000
0.133	2.000
0.106	1.995
0.094	1.995
0.089	1.995
0.088	1.995
0.081	1.995
0.079	1.995
0.078	1.995
0.069	1.995
0.056	1.995
0.037	1.995
0.017	1.995
0.013	1.995
0.012	1.995
0.005	1.995
0.003	1.995
0.002	1.995
0.000	1.995

1. running formula function =rsq(y,x)= 0.556231039
2. run chart and trend, then display rsq value on chart gets .7476
3. running data analysis, regression, gets R Square 0.526387388

 

[iknowu99]

i saw your code to extract the r^2 from the chart but that seems tidious and inefficient. I am in vba mode and plan to avoid having to graph the data every time...looking for method to calculate the linear regression r^2

i found this site that shows the same problem i'm having, r^2 don't match:
http://phoenix.phys.clemson.edu/tutorials/excel/regression.html

 

[mtskv5]

=CORREL(A:A,B:B)^2

That should give to the r-squared

 

[tusharm]

That article had a *lot* more information than the VBA code.

But, more important is that, like I wrote, if you plot the data you will see you have a *step* function. Trying to fit any other function to it is an exercise in futility.

So, the question is why are you even trying? {grin}

i saw your code to extract the r^2 from the chart but that seems tidious and inefficient. I am in vba mode and plan to avoid having to graph the data every time...looking for method to calculate the linear regression r^2

i found this site that shows the same problem i'm having, r^2 don't match:
http://phoenix.phys.clemson.edu/tutorials/excel/regression.html

 

[mtskv5]

think the graph issue is that you have the same values for y on multiple x values.

 

[mtskv5]

If you flip the x and y axies it will reflect the correct result on the chart equation and r^2 value.