I recently read an article in the Wall Street Journal about the expected increase of electric vehicles in the US. That made me wonder whether our production of electricity is keeping up with the expected increase in its demand.
So I went to the Federal Reserve Economic Database, FRED, and searched for “electricity production”. That lead me to Industrial Production: Utilities: Electric and Gas Utilities—which was the closest match I could find.
I changed FRED’s indexes to display the data as “Percent Change From Year Ago”, downloaded that version of the data, and set up my own chart, like this:
I thought I could see a pattern in that volatile data. But to be sure, I needed to smooth the data. One method that’s always worked well for me with monthly data is to use a Centered Moving Average (CMA) for smoothing. By using a 12-month average, we eliminate any seasonal variations, and we usually get a result that fits the original data very well.
(You might also be interested in How to Smooth Data, Using the TREND Function.)
I set up a column with a quick-and-dirty 12-month centered-moving-average calculation, and added that to the chart, like this:
As you can see, the blue 12-month CMA line does a great job of plotting the trend through that volatile data. But how did I calculate a centered moving average of 12 months of data?
As I explained in How to Create a Rolling Forecast of Seasonal Sales in Excel, a year has an even number of months. So when we try to calculate the CMA for the twelve months of January through December, we have this problem:
This is a problem because the average of the “sales” (the growth rates in this case) for January through December doesn’t apply to either June or July. (And no, we can’t cheat by arbitrarily assigning the average to one of the two months.)
To get around this problem, we also include the data for February through January of the following year in our average, as shown here:
To perform this calculation, I set up a formula—which you can see in the top-right of the image below—a formula that averaged the data just like the image above illustrates:
This is the classic method of calculating a monthly CMA. But there’s a problem with it when we use it with Excel’s AVERAGEIFS function. This is because it would require calculation logic like this:
=AVERAGE( AVERAGEIFS( Jan-Dec data ), AVERAGEIFS( Feb-Jan data ) )
Unfortunately, there are three problems with that approach. First, it’s a lot of work to set up. Second, in use, its formula would be very long.
And third, an average of averages usually is incorrect. If I remember my high school math correctly, an average of averages is correct only when both averages have the same number of items. But in the first and last six items, that isn’t true, so it’s best to avoid an average of averages entirely.
Therefore, what I’m now doing instead is to calculate a 13-month CMA. Using a quick-and-dirty formula, I get this result:
That is, I average the range of data that begins six months before the current month and ends six months after the current month. That lets me use a formula with one AVERAGEIFS function, a formula that looks somewhat like this:
=AVERAGEIFS( DataColumn, DateColumn, “>=” & EDATE(CurrentDate, -6),
DateColumn, “<=” & EDATE(CurrentDate, 6) )
(The download workbook includes the actual formula.)
With both smoothing methods, fewer and fewer data points are in the calculated average when the calculation is within six data points or less from either the beginning or ending of the data.
And the effective difference between the two methods is trivial: With the classic approach, February through December are weighted more heavily than either the beginning or ending January values. But with the 13-month approach, all 13 months have equal weights.
For most purposes, again, that’s a trivial difference. So, when we compare the two smoothing methods in a chart, we see a trivial difference between them:
Here’s the bottom line: If you want to use AVERAGEIFS to smooth your data by calculating a centered moving average, use the 13-month approach. It’s a lot less work to set up. Your formulas will be less than half as long. And your visual results will be just as good.
