Trendline Equation

Posted by Mike on December 27, 2001 7:14 AM

I have an XY scatter plot with a Trendline and a displayed equation. Is there any way to access the equation from VBA?

Thanks

Posted by bob Umlas on December 27, 2001 8:16 AM

You need to get the coefficients you want from LINEST. If it's a simple straight line, then you can get m & b of the equation y=mx+b by entering =SLOPE(y-values,x-values) (that's m) and =INTERCEPT(y-values,x-values), gives b. These values should match the equation's values and you can get them bu application.worksheetfunction.slope(...)
HTH

Posted by Mike on December 27, 2001 10:20 AM

Unfortunately it's not a straight line. It's normally a 2nd degree polynomial.

Will the method you gave still work?

Thanks again...Mike

Posted by Mike on December 27, 2001 11:52 AM

I guess what I'm asking is, is the trendline equation that is displayed an object that I can access with a .text property?

-Mike

Posted by Mike on December 27, 2001 3:57 PM

I figured it out:

a = Worksheets(1).ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1).DataLabel.Text

Thanks...Mike

Posted by cris on February 11, 2002 11:06 AM

Try this:
End Sub
Sub TrendLabel()
ActiveSheet.ChartObjects(1).Activate
With ActiveChart.SeriesCollection(1).Trendlines(1)
.DisplayEquation = True
.DisplayRSquared = False
.DataLabel.NumberFormat = "#,##0.0000000"
Worksheets("Sheet1").Range("d1").Value = .DataLabel.Text
.DisplayEquation = False
.DisplayRSquared = True
.DataLabel.NumberFormat = "#,##0.0000000"
Worksheets("Sheet1").Range("d2").Value = .DataLabel.Text
End With
End Sub