Sometimes Our Data Looks Like This…

 

 

We wan’t to analyze the overall trend of the graph but it’s hard because there is too much going on. Do you see all the minor fluctuations of data points along the line? These may be important when looking at individual days but we want to observe the long-term trend of the data. These fluctuations are called noise and it is unnecessary when looking at the bigger picture in data.

 

Our Goal Is To Smooth Out The Data

We can smooth out data using a technique called the moving average. A moving average is where we take the average value of data clusters throughout our dataset. A 7-Day moving average for example means we take the average of 7 data points throughout our data set. It looks something like this…

 

 

After taking a moving average you can see the data is smoothed out a little bit. There is less noise in the orange moving average line when compared to the original data which is represented by the blue line. The smaller the moving average the less smooth the data will be.

Let’s Increase The Moving Average

 

 

Here is a 21-Day moving average. As you can see, our moving average line is much smoother, the is virtually no noise. We can easily analyze the overall trend of the data without getting confused by unnecessary noise. This is the overall idea behind moving averages.

There Is A Tradeoff

Because we are taking the average of datapoint clusters the data on the tail ends are being lost. In the 21-Day moving average graph above do you notice the tail-end of the orange line doesn’t reach the end of the blue line? Every time we increase the moving average the data gets smoother but we lose more data points. This is because we are taking the average and if the moving average expands beyond the data it doesn’t work because the data doesn’t exist. Check out this photo…

 

The highlighted section is the average of those 7 data points. The average function in Microsoft Excel is used in cell C 249 because that is the midpoint of that cluster of data. We are taking the 3 data points above and below the midpoint. If we extended our moving average column (Column C) down to row 52 and take the average our values will be incorrect because it will be capturing blank cells, not actual data. Look at the photo below…

 

Here you can see if we extended the data all the way down the average of blank cells is being taken which is why we must sacrifice these last three data points.

Key Takeaway

Any data which has a lot of noise can be smoothed out using moving averages. The only caveat is that you are sacrificing data points which makes it harder to predict where the data is headed. In spite of losing a bit of our data, smoothing makes for an easy way to see the overall trend of the data by filtering out unnecessary data.

Check out my video tutorial on how to create a moving average in Microsoft Excel.