Extracting R Sqaured value from a graph using CODE

[TimeToSplit]

Hi all,

I'm a bit stuck at the moment. I must graph a series of data points in an X-Y scatter point chart multiple times, then fit every type of trendline to that a seperate graph. Using VBA, I must then extract the R Sqaured value from each trendline, and find the best trendline suitable for the job.

I have no idea how to extract the R Sqaured value from the chart though, I have looked all around on forums online and in the Excel Help, and I'm still really stuck.

Thank you in advance,

TimeToSplit

 

[davesweep]

Hi,

You don't need to extract the R2 value from the chart, it can be derived from the worksheet function RSQ.

Assuming x's in A1:A5 and y's in B1:B5

R2Linear = Application.WorksheetFunction.RSq(Range("B1:B5"), Range("A1:A5"))
R2Exponential = Application.WorksheetFunction.RSq(Ln(Range("B1:B5")), Range("A1:A5"))
R2Log = Application.WorksheetFunction.RSq(Range("B1:B5"), Ln(Range("A1:A5")))
R2Power = Application.WorksheetFunction.RSq(Ln(Range("B1:B5")), Ln(Range("A1:A5")))

 

[TimeToSplit]

Thanks, that does make it a lot easier. However, the macro won't compile now because of the Ln before the range in the exponential, log and power equations? I'm not sure why? I'm using excel 2010.

 

[TimeToSplit]

If it helps, the error I get from excel is:
Compile Error:
Sub or Function is not defined

 

[davesweep]

Sorry - I was assuming some read across from WB functions into VBA

This will do it for you, assuming your data is in A1:B5, with 5 points - adjust as required.

Sub a()
Dim i As Integer

Dim x(1 To 5) As Double, y(1 To 5) As Double, xLog(1 To 5) As Double, yLog(1 To 5) As Double

'fill arrays
For i = 1 To 5
x(i) = Range("A" & i).Value
y(i) = Range("B" & i).Value
xLog(i) = Log(x(i))
yLog(i) = Log(y(i))
Next

'derive r squared
R2Linear = Application.WorksheetFunction.RSq(y(), x())
R2Exponential = Application.WorksheetFunction.RSq(yLog(), x())
R2Log = Application.WorksheetFunction.RSq(y(), xLog())
R2Power = Application.WorksheetFunction.RSq(yLog(), xLog())

End Sub

 

[TimeToSplit]

Thank you so much! It works perfect