average/trendline in chart

  • Thread starter Thread starter DC Gringo
  • Start date Start date
D

DC Gringo

Hello group,

I'd like some help drawing a trendline in Excel. We have price datasets
going back five years that we want to chart using a line, with an
accompanying trendline. The trendline should be a moving average that uses
the same month for each year to determine the average. For example. If
January 2007's price is "5" and the previous four years' January values were
4, 4, 5, 5 (for an average of 4.5), then 4.5 should be the trendline value
for January 2007.

I'm only finding options in the chart wizard and tools for the standard
moving average of a given range (such as x months).

Please help!

DC G
 
Produce an appropriate data series in your worksheet, and use the TREND
function.
 
Hi DC,

1. You can try the Charting engine's trendline tool. On the chart select
the series and choose the command Chart, Add Trendline. You will see a
Moving Average option. Try this out and see if it is good enough.

2. A second possibility - Choose Tools, Add-in and turn on the check beside
Analysis ToolPak, click OK. In the spreadsheet choose the command Tools,
Data Analysis, Moving Average, and see if this tool helps you.
 
For example.  If
January 2007's price is "5" and the previous four years' January
values were 4, 4, 5, 5 (for an average of 4.5), then 4.5 should be
the trendline value for January 2007.

I'm only finding options in the chart wizard and tools for the
standard moving average of a given range (such as x months).
Click to expand...

I would simply set up a data series of the desired moving average,
then chart the data series. For example, if you have monthly data in
column A, the following computes a 5-period year-over-year moving
average, starting in B49:

=average(A1+A13+A25+A37+A49)

When you copy down, references get incremented appropriately.

Note: A moving average merely smooths the data fluctuations.
Notwithstanding Excel terminology and others', it is not unreasonable
to chart a suitable trendline (e.g. linear or exponential) through the
moving average data points. To me, that is the real trendline, not
the moving average curve.
 
Back
Top