Moving Average


Time series analysis is based on a set of observations taken at specific times, usually at equal intervals. The data may (and usually does) express cyclic, seasonal and irregular movements that often can mask a long term trend.

'Moving averages' is one way of smoothing the time series to expose the trend. Given a set of numbers X1,X2,X3,..., we define a moving average of order 'N' to be the sequence of arithmetic means. Thus, for example, for the sequence 4,6,8,7,5 with N=3 the moving average is expressed by the sequence (4+6+8)/3, (6+8+7)/3, (8+7+5)/3 (or 6,7,6.66).

Be aware that there are disadvantages to using this method:

A related method of estimating trends is the Least Squares linear regression method.

Script operation

This tool operates in a similar way to others but there are some exceptions which need to be taken into consideration. The first of these is that this tool will only accept raw data arranged in a single column. The second is that during the script operation a further requestor will appear to demand the 'number of intervals' (the default is 3).

Click here for information about general script usage.

Note that any heading for your column data may be included in the data input range and will then appear in the sheet output. Typical input and output is shown below:

 Raw data:      Spreadsheet output:

 Sample 1       Moving Average
      420
      650       Sample_1
      800
     1420       Forecast    Std.Error
     1360              0	    0
     1600              0	    0
     2110         623.33	    0
     2400         956.66	    0
		 1193.33      304.003
		    1460      298.017
		    1690      276.193
		 2036.66      333.729

		Interval:           3

Interpretation

To see how this analysis works, it would be best to plot both the original sample values and the Forecast values. Note that the Forecast values should be plotted midway between the first and last value of each sequence, which in this example, is a sequence of 3 values. So the plot should have the pairs 650:623.33; 800:956.66; etc.



Back to Main Document