30 Day Moving Average on 30 Day Bar Chart

[Guest]

Greetings:

I want to add a 30 day moving average trendline to my
30 day bar chart. The problem is that to correctly com-
pute the moving average for all 30 days, I need to use
the last 60 days of data even though I only want to plot
the last 30 days on the chart.

How can I use the last 60 days of data to compute this
trendline while only plotting the last 30 days as bars on
the chart?

Respectfully,

Charles

 

[Del Cotter]

How can I use the last 60 days of data to compute this
trendline while only plotting the last 30 days as bars on
the chart?

The trick is to realise that, alone of all the possible calculations
that you can do with your data, trend lines are controlled at the chart.
This fools a lot of people into thinking they need to treat Excel as a
Chart program with a little cell-based data entry facility. It's not,
it's a spreadsheet with a little chart display facility.

Make your moving average in your spreadsheet using the AVERAGE()
function, and plot the thirty days of that, along with the thirty days
of data. Best to put a little text box note on the chart as well,
explaining to the reader of the chart what you did.

 

[Guest]

Okay, this sounds like a good idea - have a separate series to
plot the 30 day moving average and compute the values of
this series with the AVG function.

However, this still leave one problem: how to draw this second
series on the chart as a line instead of a bar. Is this possible?
Can I format one series on a chart as a bar while I format the
other as a line?

 

[Guest]

Charles,

Once you have your second series set up as bars, you can change the bars to
a line by doing the following:

Click once on any of the bars that make up the second series.
Choose "Chart" from the toolbar.
Go to "Chart Type".
Choose "line" and hit "OK".