extracting R-squared value from a chart

[mikelee101]

Hello,
I have a scatter chart with a power trendline. The R-squared value is
displayed. I did find formulae that I can use to calculate the c & b value
(for the equation y=c*x^b) directly from the data. My question is whether
there is a formula that I can use to either:

a-extract the R-squared value from the chart, or
b-calculate the R-sqared value directly from the data and equation values (c
& b).

I hope that makes sense. It's been a looooong time since I've used
Statistics, so I may have used some terms incorrectly. If so, I apologize.
Let me know and I can clarify.

Thanks for the help.

 

[Bernie Deitrick]

Mike,

=CORREL(Xvalues,YValues)^2

or

=RSQ(YValues,XValues)

See also help on LINEST - there is more available in that help than in the CORREL help.

HTH,
Bernie
MS Excel MVP

 

[mikelee101]

Hello Bernie,
Thanks. I did look at RSQ (and CORREL after reading your post), and that
seems to apply to linear regression (y=mx+b). My line is y=c*x^b. When I
tried both of them, they returned .41...., which was different from the
displayed value on the chart.

I also read the help on LINEST, and tried adding a couple of columns to my
data. I calculated sstotal by squaring the known y's and summing that
column. Then in another column calculated ssresid by using my c & b values
to calculate the expected y for each known x, taking the difference from the
actual y and squaring that, then summing that column. ssreg was
sstotal-ssresid. Then I took a shot at r-squared by ssreg/sstotal.
Unfortunately, that gave me a value of .93.... when the r-squared value on
the chart is displayed at .81.... I suspect that the difference may lie in
the fact that I'm using a power line as opposed to a linear one.

I hope that makes sense. Since Excel can calculate it in the chart, I have
to believe there's a way to calculate it in a cell. The math is just a bit
beyond me.

Any other suggestions would be greatly appreciated.

Thanks again.

 

[Bernie Deitrick]

Try it taking the log of each side:

y = c*x^b
log y = log c + b * log x


HTH,
Bernie
MS Excel MVP