asked on

How do I get the final projected value when using the Excel VBA Trendlines.Add method to add a trendline to a chart?

How do I get the final projected value when using the Excel VBA Trendlines.Add method to add a trendline to a chart?

The trendline on the chart is created via:

ActiveChart.SetSourceData Source:=Range(Data_Range)

ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveChart.SeriesCollection(1).Trendlines(1).Forward = 90 ' Forecast 90 days into the future
ActiveChart.SeriesCollection(1).Trendlines(1).Name = "Proj." ' Set Legend name for projected data line.

There is no direct property that I can find for the Trendlines method that gives you the final projected value of the Trendline.
That is the value I need.

Any help on this is greatly appreciated.

Patrick Matthews

The Trendline object has nothing like a Points collection which would allow us direct access to this.

Theoretically, you could use the LINEST() function to get the regression equation for the trendline; the first element returns the coefficient, and the second returns the intercept value. You would then be able to use the equation to compute the forward value.

The attached workbook shows a simple example of this.
Q_28997884.xlsm

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toddvoros

ASKER

Very good solution. Only problem is LinEst does not like discontinuous dates, so you must change Ser.xValues to observation numbers, run the Application.WorksheetFunction.LinEst, and then change it back to dates again.