All VBA Kung foo masters, I need to be able to use the
Equation of a line in my code to calculate a forecast on
some sales data. I know that on the Chart I can add a
trendline, and then tell it to 'display equation on
chart', but how can I get the value of that equation?

I actually want to use that equation in some code and
since Excel is displaying it there should be a way to get
the value of that data label

I tried this but to no avail:
ActiveChart.SeriesCollection(1).Trendlines
(1).DataLabel.Value????

Any one got the Kung Foo move for this?

Alex A

basically, you're better off using worksheet functions
rather than the chart text string.

Use this format:

slope= application.worksheetfunction.slope(range
("a1:a50"),range("b1:b50"))

I wrote some killer code to automate the whole process.

try this url for the other trendline syntaxes-
http://www.j-walk.com/ss/excel/tips/tip101.htm


-----Original Message-----
All VBA Kung foo masters, I need to be able to use the
Equation of a line in my code to calculate a forecast on
some sales data. I know that on the Chart I can add a
trendline, and then tell it to 'display equation on
chart', but how can I get the value of that equation?

I actually want to use that equation in some code and
since Excel is displaying it there should be a way to get
the value of that data label

I tried this but to no avail:
ActiveChart.SeriesCollection(1).Trendlines
(1).DataLabel.Value????

Any one got the Kung Foo move for this?

Alex A
.

I used the two functions slope and Intercept but for some
reason the intercept is coming out wrong.

M = Application.WorksheetFunction.Slope(Range("e25:e28 "),
Range("c25:c28"))

b = Application.WorksheetFunction.Intercept(Range
("e25:e28"), Range("c25:c28"))

The label on the graph shows
y = 14484x + 562698

my slope is 14483.664 (good)
my intercept is -28390146.1562596 (bad)

Thoughts?:
One cell range has the Excel Number datatype
and the other cell range is the Excel Currency datatype

Alex -

Is your chart a line chart? It will calculate these parameters based on
pseudo X values of {1,2,3,4,5,...}. If your true X values increment by
1, but start far away from 1, your slope will be right but the intercept
will be a mile off. Is your first X value close to 2000?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Alex A wrote:

I used the two functions slope and Intercept but for some
reason the intercept is coming out wrong.

M = Application.WorksheetFunction.Slope(Range("e25:e28 "),
Range("c25:c28"))

b = Application.WorksheetFunction.Intercept(Range
("e25:e28"), Range("c25:c28"))

The label on the graph shows
y = 14484x + 562698

my slope is 14483.664 (good)
my intercept is -28390146.1562596 (bad)

Thoughts?:
One cell range has the Excel Number datatype
and the other cell range is the Excel Currency datatype