|This tutorial shows
an interactive moving average trend chart. It is based on the example found in
1. Moving Average Charts
Detailed process data can include diurnal (daily), weekly, monthly and
seasonal variations as well as spike events that can make evaluation of trends
challenging. Excel's moving average tool can help to evaluate underlying data
trends by averaging out the dynamic fluctuations. This example chart shows daily
flow data for 18 months. This data has a weekly as well as a seasonal pattern.
We can add a moving average with Excels Chart Add Trendline window to help us
separate the longer term trends from the day-to-day fluctuations.
Let's see what a 30 day moving average looks like.
The chart on the left shows both the detailed data and the 30 day moving
average. The chart on the right shows just the 30 day moving average.
To me, the chart on the right gives a clearer of the long term trend.
The example file lets you vary the time period, moving average period and to
toggle the detailed data on and off. Why not try it to get some ideas about how
to set up interactive charts.
2. Adding Interactive Capabilities
When analyzing a time series data set, it is often helpful to adjust one or
more parameters and see what happens. In our moving average example, we may want
to change date range, date format or interval on x axis, moving average time
period or display of moving average line or data line. It would be nice to be
able to make a simple change and have Excel redraw our chart so that we can
interact with the chart, cycle through a series of changes to help us see if
there are any underlying patterns that may be important to us.
We can build interactive charts with a combination of Excel's event handling
capabilities and VBA. In this example, I created a control table where all of
the adjustable moving average chart variable are stored Cells B3:C3 store the X
axis minimum and maximum chart dates. Cells C4:C5 store the X axis
major unit and number format.
Cell C6 stores the moving average period and cell C7 has a On/Off list to
allow User to turn data display on or off.
Control X Axis
Min - Max Values provides information on how to set the min
and max x axis dates for a trend chart.
Chart_Mov_Avg.XLS. workbook includes VBA code to generate the
interactive moving average chart. Reviewing this code may give you some ideas on
how you could automate your charts.
A worksheet change event procedure is used to monitor the colored cells noted
above. Any change in one of the colored cells triggers a worksheet change event
and call the appropriate procedure to update the chart. This way, the user can
make a change in date or interval and the chart is immediately updated with the
new set of chart conditions.