dynamically changing the forward plot on a trend line

[Gandalf]

Hi All


Can anyone help me? I have an number of excel line charts which dynamically change when I add new data every month. This data also has a trend line which I would like to always forecast forward a fixed number of months i.e. it would always forecast to 18 months from the first data point on the x axis regardless of how many data points have been added. Can this be done without having to manually change the forward forecast on each chart?


Any help or advice would be appreciated - thank you

 

[Hui]

If you use Named Formula for the Ranges for the Chart

eg:

[pre]

Date: =Offset(Sheet1!$A$1,,,Counta($A:$A),1)
Data: =Offset(Date,,1)

[/pre]
Then use the Named Formula names for the Chart, not the direct ranges

When you add/subtract data the chart will automatically uppdate and

the Trendline will stay 18 periods ahead of the data

 

[Gandalf]

Hi Hui


thank you for you reply. I probably didn't explain myself very well. I use the offset named formula but want the trend line to always finish at a set point. I have data going initially from say January 2012 to say January 2013 and the trend line forecast is set to go forward 6 months from January 2013 to July 2013 by entering 6 in the forecast-forward box in format trend line. What I would like to do is enter data for February 2013 and the forward forecast will only add 5 more months (still a total of 18 months from January 2012, the same as entering 5 in the forecast-forward box, rather than an end point of 19 months from January 20120.


This is probably not any better explained.


Again, many thanks


Gandalf

 

[Hui]

Gandalf


There is no easy or formulaic approach to solving this


It can be done using VBA via

[pre]

Dim Fwd as Integer
Fwd = 5
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Forward = Fwd

[/pre]
Obviously the VBA will need to have some code around it to understand what your doing and set the value for Fwd (5) as appropriate


Can you post a sample file ?

 

[Gandalf]

Hello


Not sure if this will work but...

https://www.dropbox.com/sh/uen2wdj2kf64za6/HN0cSgKsan/Test%20variable%20range%20sample.xlsm


thank you


Gandalf

 

[Gandalf]

Hello All


Finally managed it. I'm sure that all of you can do it better so if anyone has any suggestions as to how the code could be improved I would appreciate it.

"

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(1).Trendlines(1).Select

Selection.Delete

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(1).Trendlines.Add Type:=xlLinear, _

Forward:=26 - Application.WorksheetFunction.CountIf(worksheets(1).Range("Student"), ">=0"), _

Backward:=0, DisplayEquation:=0, DisplayRSquared:=True, Name:="Trend"

ActiveChart.SeriesCollection(1).Trendlines(1).Select

ActiveChart.DisplayBlanksAs = xlInterpolated

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(255, 0, 0)

End With

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select

Selection.Left = 740.108

Selection.Top = 27.832

Range("Z2").Select

"


Many thanks