average/trendline in chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DC Gringo - 03 Jan 2008 00:06 GMT

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

Reply to this Message

David Biddulph - 03 Jan 2008 00:24 GMT

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

Signature

David Biddulph

> Hello group,
>
[quoted text clipped - 12 lines]
>
> DC G

Reply to this Message

ShaneDevenshire - 03 Jan 2008 03:41 GMT

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.

Signature

Cheers,
Shane Devenshire

> Hello group,
>
[quoted text clipped - 12 lines]
>
> DC G

Reply to this Message

joeu2004 - 03 Jan 2008 04:37 GMT

> For example. If
> January 2007's price is "5" and the previous four years' January
[quoted text clipped - 3 lines]
> I'm only finding options in the chart wizard and tools for the
> standard moving average of a given range (such as x months).

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.

Reply to this Message