Excel trendline - get the R2 value by code
 

Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

Excel trendline - get the R2 value by code
 

I am interested in this too, so doing an entry to see the replies.

"Daniel Bonallack" wrote:

Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

Excel trendline - get the R2 value by code
 

Daniel -

The trendline is based on Known Y's and Known X's.

So you could use the worksheet function RSQ to get the r-squared value.

For example,
=application.worksheetfunction.rsq(range(KnownY's) ,range(KnownX's))

- Mike
www.mikemiddleton.com

"Daniel Bonallack" wrote in
message ...
Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

Excel trendline - get the R2 value by code
 

Thanks Mike

Does this mean that I can actually calculate the R2 on the workbook using a
formula?

Daniel


"Mike Middleton" wrote:

Daniel -

The trendline is based on Known Y's and Known X's.

So you could use the worksheet function RSQ to get the r-squared value.

For example,
=application.worksheetfunction.rsq(range(KnownY's) ,range(KnownX's))

- Mike
www.mikemiddleton.com

"Daniel Bonallack" wrote in
message ...
Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

Excel trendline - get the R2 value by code
 

Daniel -

In a worksheet cell, you can "manually" enter =RSQ(Yrange,Xrange).

You posted in the Programming newsgroup, so if you want to do this in VBA,
you use MyRSQ = application.... as shown in my previous post.

You can also get R-Squared using the LINEST worksheet function.

- Mike
www.mikemiddleton.com

"Daniel Bonallack" wrote in
message ...
Thanks Mike

Does this mean that I can actually calculate the R2 on the workbook using
a
formula?

Daniel


"Mike Middleton" wrote:

Daniel -

The trendline is based on Known Y's and Known X's.

So you could use the worksheet function RSQ to get the r-squared value.

For example,
=application.worksheetfunction.rsq(range(KnownY's) ,range(KnownX's))

- Mike
www.mikemiddleton.com

"Daniel Bonallack" wrote in
message ...
Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

Excel trendline - get the R2 value by code
 

Yes you can calculate the R Squared without having to resort to VBA. You can
also smooth your source data using polynomial regression analysis with Trend.
There is a linest function and a forecast function in Excel that are kind of
cool. To be completely thurough there is also slope and intercept. If you
don't see what you need in this list just reply back and we can go through a
few more graphing/stats functions...
--
HTH...

Jim Thomlinson


"Daniel Bonallack" wrote:

Thanks Mike

Does this mean that I can actually calculate the R2 on the workbook using a
formula?

Daniel


"Mike Middleton" wrote:

Daniel -

The trendline is based on Known Y's and Known X's.

So you could use the worksheet function RSQ to get the r-squared value.

For example,
=application.worksheetfunction.rsq(range(KnownY's) ,range(KnownX's))

- Mike
www.mikemiddleton.com

"Daniel Bonallack" wrote in
message ...
Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

Excel trendline - get the R2 value by code
 

Would this example help.

Sub Demo()
Dim v1
Dim v2
With
Worksheets("sheet1").ChartObjects(1).Chart.SeriesC ollection(1).Trendlines(1)
.DisplayEquation = False
.DisplayRSquared = True
v1 = .DataLabel.Text
v2 = Val(Split(v1, "=")(1))
End With
MsgBox v1
MsgBox v2
End Sub

Where v1 is the text, and v2 is just the number:
?v1
R2 = 0.9909

?v2
0.9909

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"David" wrote in message
...
I am interested in this too, so doing an entry to see the replies.

"Daniel Bonallack" wrote:

Hi, I have an Excel scatter chart, that has a trendline and R2 value.

How can I return the R-squared value of the trendline to a variable

I thought:
x = ActiveChart.SeriesCollection(1).Trendlines(1).Data label.Value

but this doesn't work.

Please help!
Thank you very much

Daniel