Microsoft Office Tutorials and References

In Depth Information

**Creating your own moving average data series**

A moving average is useful for smoothing out noisy data; it may also help to

uncover trends that may otherwise be difficult to spot. Figure 5-16 shows a line

chart with 50 data points, along with a moving average line with a period of 7 (it

displays the average of every seven data points). As you can see, the moving

average line is much smoother and clearly depicts the general upward trend in the data.

Figure 5-16: This chart displays a moving average.

Notice that the moving average line does not begin with the first data point. In

this case, the line begins at the seventh data point because the period is 7. The

beginning of the line is the average of the first seven data points. The second point

is the average of data points 2 through 8, the third point is the average of data

points 3 through 9, and so on. Generally, using a larger period will result in a

smoother line — but the line gets shorter as the period increases.

Creating your own moving average data series

You can, of course, create formulas to calculate a moving average for a data series,

and then plot the moving average as a separate chart series. For example, assume

that your data is in the range A1:A50. To create a moving average with a period of

7, enter this formula into cell B7:

=AVERAGE(A1:A7)

Then, copy the formula down the column, ending with cell B50. Add B1:B50 as

a new data series and the result will be identical to adding a moving average line

via the Add Trendline dialog box.

This technique offers two advantages: You can add a moving average line to

chart types that don’t support trendlines, and you have more control over the

appearance of the moving average line.

Figure 5-17, for example, shows a 3-D line chart (this type of chart does not

support trendlines). The chart displays an additional series with a calculated

moving average.