Linear Forecast Trendline based on a portion of the series

**Bob_Hunt** · 08-19-2015, 02:50 AM

I have a data series on my chart for which I would like to plot a Linear Forecast Trendline.

a) I would like to plot this trend line only based on the last several (10) data points in the series. The trendline will forecast 8 periods into the future.

b) Ideally, I would like this forecast to operate as a sort of moving forecast (i.e. As additional data is added to my series it should automatically project the trend from the most recent 10 data points) without having to manually manipulate the parameters of the trendline.

Is it possible to due either / both of these in excel?

Thanks for any help with this!

**humdingaling** · 08-19-2015, 03:32 AM

if i understand what you are trying to do
you want to map trend as data on the series? if so then trend will do linear forecast projections

see attached

**Bob_Hunt** · 08-19-2015, 08:09 AM

Thanks for getting back to me!
What I am looking for is a bit more complicated.....

Yes, I want to map trend data as a series but with a caveat.
The typical 'Linear Forecast Trendline' draws the trend / forecast for all data points in the series. I would like to have it drawn using only the last 10 data points (i.e. ignore the earlier data points).
Please see the attached to illustrate the problem.....

In the Chart a 'typical' trendline (Blue line) is shown. It is based on all datapoints in the series and forecasts out for 8 periods.
The Red dashed line I have calculated and added manually to the chart. It represents the future trend/ forecast based on the last 10 samples.
I would like to, somehow, generate this trend automatically.

I hope this makes sense!

Burnup Chart.xlsx

**MrShorty** · 08-19-2015, 10:50 AM

Where to begin....

It appears that you are doing these regressions using the chart trendline function, and did not seem very interested in humdingaling's approach using worksheet regression functions. One thing to realize is that a chart trendline must use the entire data series that it is based on for its regression. This means that the way to get the chart trendline to regress on a subset of the data, you need to add an additional data series that contains just that subset of the data.

Perhaps the easiest way to do this is to add a helper row to your table. Somewhere below the lowest used cell (I tested in row 401), a formula like =if(condition(s) to decide if this data point is to be included,i$4,na()). Note that I use the NA() function as described in this article on how charts handle "simulated" empty cells: http://peltiertech.com/mind-the-gap-...g-empty-cells/ ). Then you can add this row as an additional series, then base your trendline on this series. Repeat for each trendline based on different subsets of the data that you want to show.

**Bob_Hunt** · 08-19-2015, 11:30 AM

Thank you for the tip! I will experiment a bit with it to see if I can get to to perform properly for me!

Linear Forecast Trendline based on a portion of the series

LinkBack

Thread Tools

Rate This Thread

Display

Linear Forecast Trendline based on a portion of the series

Re: Linear Forecast Trendline based on a portion of the series

Re: Linear Forecast Trendline based on a portion of the series

Re: Linear Forecast Trendline based on a portion of the series

Re: Linear Forecast Trendline based on a portion of the series

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel 2010 Trendline With Data labels or number on forward forecast trendline

How do I get my linear trendline to stop at zero?

Trendline Forecast

Finding range of linear portion of data

How to forecast value for a non-linear curve

Trendline based on more than one series

TRENDLINE based on ALL the series

Linear Trendline

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions