Extracting R-square from Chart into cell

Question

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.

I know we can calculate directly using RSQ and LINEST, but when intercept is taken zero value form chart is not same as calculated through LINEST. And I want to document the values from chart.

linear trend line only – meetesh goyal Sep 25 '15 at 08:37 — meetesh goyal, Sep 25 '15 at 08:37

Bas Verlaat · Answer 1 · 2015-09-26T10:32:16.573

3

you can try:

x = Val (Split(ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text, "=")(1))

If the formula is displayed as well, then use the third element (id 2)

x = Val (Split(ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text, "=")(2))

To be absolutely sure about the position, you can use this:

Public Function GetR2() As Single

    Dim sArray() As String

    sArray = Split(ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text, "=")

    GetR2 = CSng(sArray(UBound(sArray)))

End Function

If you want to stick to one line, then this will work in both situations as well

x = Val(Split(ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text, "R² =")(1))

edited Sep 26 '15 at 10:32

answered Sep 25 '15 at 11:13

Bas Verlaat

842
6
8

This is a very nice attempt, but it should be pointed out that this [fails when the linear fit equation is displayed alongside the R² in the chart](http://i.imgur.com/Gr8WKKy.png). – Jean-François Corbett Sep 25 '15 at 14:23
I think I see what you mean. If the formula is displayed as well, then use the 3rd element item after split like this: `x = Val (Split(ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text, "=")(2))` – Bas Verlaat Sep 26 '15 at 10:20
I've added it to my answer. Thanks for the addition – Bas Verlaat Sep 26 '15 at 10:27

Jean-François Corbett · Answer 2 · 2015-09-25T15:00:29.880

This works:

Dim iR2 As Long, iEqual As Long
Dim linearFitLabel As String
Dim strR2 As String
Dim r2 As Double

'Get the trendline label; may contain equation in addition to R²
linearFitLabel = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text

iR2 = InStr(linearFitLabel, "R²") ' find string "R²"
iEqual = InStr(iR2, linearFitLabel, "=") ' find equal sign after that
r2 = CDbl(Trim(Mid(linearFitLabel, iEqual + 1))) ' convert everything after = into a number
MsgBox "R² displayed on chart: " & r2

Note, however, that the value returned will be dependent upon the number formatting of the R² value as displayed in the chart. So you're not going to get the actual value of R², just a value rounded off to some precision for display purposes.

Additional info

To be really clean, I would probably want to calculate R² from scratch. The formula can be written up as an Excel formula:

=1-SUMPRODUCT((B3:B10-(A3:A10*INDEX(LINEST(B3:B10,A3:A10,NOT($D$4)),1)+INDEX(LINEST(B3:B10,A3:A10,NOT($D$4)),2)))^2)/SUMPRODUCT((B3:B10-AVERAGE(B3:B10))^2)

This remains valid whether the linear fit intercept is set to zero (cell D4):

or not:

With a bit of effort, this Excel formula can be re-written as VBA.

Extracting R-square from Chart into cell

2 Answers2

Linked