Moving Average Tool
Use the moving average tool to calculate moving averages of one or more data sets. A moving average provides useful trend information of the data that is lost in a simple average. In addition, moving averages can be used to eliminate random variance. For example, use this tool to create a smoother curve of a stock prize.
Specify the cells containing the datasets in the “Input Range” entry. The entered range or ranges are grouped into datasets either by rows or by columns.
If you have labels in the first cell of each data set, select the “Labels” option.
Choose the type of moving average you would like to calculate. The tool can determine 4 types of moving averages:
- Simple moving average
- Cumulative moving average
- Weighted moving average
- Spencer's 15 point moving average
Specify the “Interval” for the moving average. The interval i is the number of consecutive values to be included in each moving average. This options is only available for the simple and weighted moving averages.
Check the “Standard errors” checkbox if you would also like the standard error to be calculated. Since there is no general agreement on the denominator for the standard error you can choose the appropriate radio button.
In the case of the simple moving average, you can also choose between a prior moving average and a central moving average, or you may even specify any other desired offset.
- “Prior moving average”: Each average takes into account the current observation and the most recent prior observations for a total of i observations.
- “Central moving average” with i being odd: Each average takes into account the current observation and the same number of most recent prior observations and closest future observations for a total of i observations.
- “Central moving average” with i being even: This is calculated according to the formula given in Figure 8-42. at is the moving average at time t and yt is the observation at time t.
- “Other offset”: If the offset is 0, this is just the prior moving average. Otherwise the offset indicates the number of closest future observations to include in the average. Correspondingly, the number of most recent past observations is decreased.
The results are given in one column for each dataset (with a second column added if you have chosen standard errors to be calculated). Each row represents the moving average of the corresponding row or column in the input range. Depending on the type of average and the offset, the moving average cannot be calculated for the first rows in the input range.
- 8.7.2.1. Simple Moving Average
- 8.7.2.2. Cumulative Moving Average
- 8.7.2.3. Weighted Moving Average
- 8.7.2.4. Spencer's 15 Point Moving Average
- 8.7.2.5. A Moving Average Example
8.7.2.1. Simple Moving Average
A simple moving average is the unweighted average of a collection of observations. Exactly which observations are included depends on whether a prior or central moving average is calculated.
8.7.2.2. Cumulative Moving Average
A cumulative moving average is a prior moving average in which the current and all prior observations are included.
8.7.2.3. Weighted Moving Average
A weighted moving average with an interval i is a prior moving average calculated according to formula Figure 8-42. at is the moving average at time t and yt is the observation at time t.
8.7.2.4. Spencer's 15 Point Moving Average
Spencer's 15 point moving average is a central moving average calculated according to formula Figure 8-44. at is the moving average at time t and yt is the observation at time t.
8.7.2.5. A Moving Average Example
Figure 8-45 shows some example data, Figure 8-46 shows the option settings, and Figure 8-47 the corresponding output.
