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:
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.
Search JabSto ::

Custom Search