Smoothing Excel chart data with a fixed moving average works when you have experience with the data and how variable it is. But, if the data changes or its new you or management may want to experiment with a different number of periods in the smoothing average.
In this example, an OFFSET formula is used to create an average across a dynamic range. You type in the number of months you want to use for a smoothing period and the AVERAGE and OFFSET calculate how many cells to average across.
Before making the AVERAGE dynamic, let’s begin by understanding how OFFSET works. In the following figure, cell G26 contains the formula for smoothing the data in row 20,
This formula is an intermediate step to help you understand how the dynamic formula works. The OFFSET function returns a range that AVERAGE will act on. OFFSET’s attributes are,
In this example, the range that OFFSET calculates starts at G20 and it has zero row or column offset. In other words, the top left corner of the range OFFSET calculates is just G20. It won’t be shifted or offset by any rows or columns. The calculated range will be 1 row high and include the current cell G26 and two more to the left (that’s a -3). So, this calculated range will be G20:E20.
Copy that formula in G26 to the right and you’ll see it averages the data cell above it in row 20 and the previous two cells to the left.
So, here’s the trick that will make this a dynamic and average across a range you choose. What if you substitute a cell reference for that -3 in the formula?
In the figure below the formula in cell G26 has been modified so the [width] is specified by the number in cell E24,
$E$24 is the green shaded cell. The number you type in cell E24 will determine the width of cells used to average across.
At this point you could use a spinner or drop-down menu to give your user a choice of how wide to make the smoothing average. The result of their selection should go in cell E24.
There’s one problem with this arrangement. If the user enters a larger number in E24 than there are cells to the left then OFFSET will include blank cells and the label. This will give an incorrect average. If E24 is so large that OFFSET goes off the sheet then AVERAGE produces an error. What to do? We need a solution that doesn’t just stop an error, like ISERROR, we need one that stops incorrect answers.
In this next figure the formula is cell G26 has been adjusted. Row 25 has a series of numbers that are the limits for valid ranges to the left. The new formula in cell G26 looks to row 25 and if the number in cell E24 is larger than the limit in row 25, then NA() is produced. This new error-correcting formula in G26 is,
When an Excel chart references NA() in a cell it plots nothing. That prevents the smoothing line from dropping to zero.
Now you can let your user try any smoothing period they want. However, (seems like there’s always a “however” or a “but, in this case”) there’s a problem with smoothing data. Smoothing takes out the volatility, but it also means you won’t see changes in direction, inflection points, or new trends until about half way through the smoothing period. You’re behind the curve.
The best of both worlds is to use a weighted average smoothing function. That way you can give more weight to near term data so you see trends, but you can smooth based on older data to eliminate some of the volatility. Sounds like a good case for another article, Smoothing Excel Chart Data Using a Weighted Average.