Excel 2019 Bible

Chapter 16: Using Formulas for Statistical Analysis

16

FIGURE 16.2

Expanding a weighted average calculation into adjacent cells

Smoothing Data with Moving Averages

A moving average is used to smooth out data to provide a clearer picture of the overall trend

of the data. It works particularly well when the individual data points are erratic. Figure

16.3 shows a partial listing of golf scores. Anyone who plays the game knows just how

erratic scores can be from one round to the next. Figure 16.4 shows a graph of the scores

over time. It’s difficult to get a sense of how this golfer’s game is changing because of the

steep peaks and valleys on the chart.

We want to create a chart that shows how the scores are progressing by smoothing out

the highs and lows. To do this, we can calculate the moving average of the scores and plot

those values on the chart.

=IF(ROW()<12,NA(),AVERAGE(OFFSET(D3,-9,0,10,1)))

This formula uses a number of Excel functions to accomplish our task. First, an IF function

is used to return the #N/A error for the first few scores. The ROW function with no argu-

ment returns the row of the current cells. We don’t want to start our moving average calcu-

lation until we have enough data, so the formula returns #N/A for the first nine rows.

Excel charts don’t show #N/A errors. The NA function is used for values that you don’t want to show on your charts.

For the later scores, the AVERAGE function is used to return the arithmetic mean of the

prior ten scores. AVERAGE takes up to 255 arguments, but since our values are a contiguous

range, we need to supply only one.