How do I create a trendline for the past 13 weeks on a 52 week chart?

Thanks,

Max McGovern

Max,

This could be one option to use to plot the most recent trend. This example
trends the last 4 days of a 10 day stretch (of course you would change yours
to look at the last 13 weeks of a 52 week time-frame). Try setting your data
up like this:


Observation Date Data Trend
1 1/1/2005 56
2 1/2/2005 67
3 1/3/2005 65
4 1/4/2005 56
5 1/5/2005 67
6 1/6/2005 68
7 1/7/2005 75 70.61818
8 1/8/2005 67 72.69697
9 1/9/2005 69 74.77576
10 1/10/2005 85 76.85455

The linear trend is calculated using Excels TREND function. Its an array
formula that is entered using the control €“ shift €“ enter keys. For more
information on this calculation, look up TREND FUNCTION in Excels help.
This example uses a linear trend but you can get other types of trends from
Excel too.

To set up the chart, assume the €œObservation€� title is in cell A1. Put your
curser in cell B1 and use the Chart Wizard to create a line chart covering
the range B1:D11.

----
Regards,
John Mansfield
http://www.pdbook.com



"Max McGovern" wrote:

How do I create a trendline for the past 13 weeks on a 52 week chart?

Thanks,

Max McGovern

Max -

A trendline can only be made with all points in a series. As an alternative to
John's suggestion, make a series with only the last 13 data points:

Observation Date Data Partial
1 1/1/2005 56
2 1/2/2005 67
3 1/3/2005 65
4 1/4/2005 56
5 1/5/2005 67
6 1/6/2005 68
7 1/7/2005 75 75
8 1/8/2005 67 67
9 1/9/2005 69 69
10 1/10/2005 85 85

Plot both series, the full set and the partial set, and make your trendline using
the shorter series. If you want, you could hide these points by formatting the
series to use no marker. The trendline will span the entire range of data, even
though it is based only on the plotted points.

To get a trendline that only spans the appropriate points, make your second series
as an XY Scatter series using only the points in the Partial column and the
corresponding dates. If you started with a line chart, select just the short series,
and choose Chart Type from the Chart menu to change its type; then choose Chart
Options from the Chart menu, and uncheck the secondary axis boxes on the Axes tab.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Max McGovern wrote:

How do I create a trendline for the past 13 weeks on a 52 week chart?

Thanks,

Max McGovern

Thank you both for your suggestions. It took me until now to figure out
John's method, but I did get it to work. I will now work on figuring out
your method, Jon.

Max McGovern

"Jon Peltier" wrote:

Max -

A trendline can only be made with all points in a series. As an alternative to
John's suggestion, make a series with only the last 13 data points:

Observation Date Data Partial
1 1/1/2005 56
2 1/2/2005 67
3 1/3/2005 65
4 1/4/2005 56
5 1/5/2005 67
6 1/6/2005 68
7 1/7/2005 75 75
8 1/8/2005 67 67
9 1/9/2005 69 69
10 1/10/2005 85 85

Plot both series, the full set and the partial set, and make your trendline using
the shorter series. If you want, you could hide these points by formatting the
series to use no marker. The trendline will span the entire range of data, even
though it is based only on the plotted points.

To get a trendline that only spans the appropriate points, make your second series
as an XY Scatter series using only the points in the Partial column and the
corresponding dates. If you started with a line chart, select just the short series,
and choose Chart Type from the Chart menu to change its type; then choose Chart
Options from the Chart menu, and uncheck the secondary axis boxes on the Axes tab.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Max McGovern wrote:

How do I create a trendline for the past 13 weeks on a 52 week chart?

Thanks,

Max McGovern