PDA

View Full Version : Trendline data/info



leal72
09-24-2009, 08:10 PM
Is it possible to grab the trendline equation using code?

I tried with Macro record to see what was going on but it just grabs the entire chart and I'm just looking to grab the equation.

p45cal
09-25-2009, 04:00 AM
Yes, but it's convoluted.
I can't find anywhere the equation is stored but the text string of the displayed formula is grabbable with the likes of:
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Caption
or:
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text

but you must have the equation displayed, even if only for a moment, to grab the string:
ActiveChart.SeriesCollection(1).Trendlines(1).DisplayEquation = True
I got this:
y = -6E-07x4 + 0.100x3 - 6034.x2 + 2E+08x - 2E+12
where x2,x3,x4 mean x squared, x cubed, x to the 4th.
If you want more accuracy, then you need to change the format of the datalabel with something along the lines of:
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.NumberFormat = "#,##0.0000"
or:
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.NumberFormat = "0.000000000000000"

which latter gave me:
y = -6.2589144717509100E-07x4 + 1.0035721678420400E-01x3 - 6.0342873669635000E+03x2 + 1.6125655239476500E+08x - 1.6159820786319000E+12

Now you have the problem of extracting values.

I'd like to think there was a better way..

leal72
09-25-2009, 07:44 AM
that's a great start though, Thank you.

Only other way I was thinking to do it, was using formulas and the data used to create the trendline to get me the result that is needed.

Bob Phillips
09-25-2009, 08:09 AM
See if this helps

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

leal72
09-25-2009, 10:22 AM
See if this helps

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

:thumb

Awesome! Thank you!