Moving Average

Adapted from the Forums:

Q: I need to know what mathematical algorithm is used for the moving average calculations.

Normally, a moving average will require that the "end regions" of the graph not be included in the graph of the moving average, since, for example, a centered 11-day moving average can't provide data for the first 5 days and therefore can't be graphed. The same is true of the last 5 days in this example. I believe this problem is inherent in all moving average calculation varieties (though I'm open to being educated).

I bring it up because graphing the end-point regions of a moving average generates quite a bit of controversy in the climate sciences where I work because the end regions normally excluded in a moving average have to be filled in based on some sort of (often unscientific) assumption--even a simple "continue the trend" assumption.

A: For versions and earlier, the output of the Moving Average plugin is an average of the last N points, where N is the interval chosen. This is identical to the Moving Average function of Excel's Analysis ToolPak, with the exception that Excel gives "#N/A" for the first N-1 points, while the DPlot plugin gives the average of i points for i<N.

The new version of the Moving Average plugin includes three forms, two of them new: 1) Prior Moving Average (identical to the only previous option), 2) Central Moving Average (more like what you're talking about, with point i equal to the average of points i-(N-1)/2 through i+(N-1)/2, and 3) Cumulative Moving Average, which is the average of all points up to and including the i'th point.

In addition, the new plugin allows you to include (or not) points at the tails of your data. If Include tails is checked, then as before the i'th point for i<N is the average of the first i points for a Prior Moving Average. Similarly, for a Central Moving Average and i<(N+1)/2 or greater than NP-(N-1)/2 (where NP is the number of points in the input), the output is the average of the sampled input points. If Include tails is not checked, then the output will only include points for which i>N for a Prior Moving Average, or i>(N-1)/2 and i<NP-(N-1)/2 for a Central Moving Average.

The new input dialog for the Moving Average plugin (licensed version only):

Which method is appropriate for your use is dependent on what you are measuring. The Prior Moving Average is a well-established standard but for data that tends to increase or decrease over time, introduces a lag in the output: for a steadily increasing record the output will tend to be equal to the input N/2 measurements ago. The Central Moving Average eliminates this lag but may not be appropriate for your use if the current prediction should not be influenced by future data. The Cumulative Moving Average is mostly useful for data that doesn't increase/decrease monotonically over time and for which early data is significant for the present.

The new plugin is included with the release of DPlot. Licensed users who are not on the update mailing list can get this version by selecting the Check for Updates command on the Help menu.

Example (with Include tails checked in all cases):

About this Entry

This page contains a single entry by David Hyde published on November 3, 2009 7:48 PM.

Help! DPlot's Help doesn't work! was the previous entry in this blog.

Calling Excel Add-In functions from a Macro is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.



Powered by Movable Type 4.25