Excel trendline - get the R2 value by code

[Guest]

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).Datalabel.Value

but this doesn't work.

Please help!
Thank you very much

Daniel

 

[Guest]

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

 

[Mike Middleton]

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

 

[Guest]

Thanks Mike

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

Daniel

 

[Mike Middleton]

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

 

[Guest]

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...

 

[Dana DeLouis]

Would this example help.

Sub Demo()
Dim v1
Dim v2
With
Worksheets("sheet1").ChartObjects(1).Chart.SeriesCollection(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 :>)