Progress Log 68 (Excel): Moving Averages

I’m taking a nanodegree course entitled “Data Analysis” by Udacity and all of the concepts described below come from that course.

Moving Averages

 

Moving averages smooth out data to make it easier to observe long term trends and not get lost in daily fluctuations. Let us first evaluate sales projections with daily data. Although informative, the chart looks too volatile to interpret because more people shop on the weekends, so sales spike on those days.

Secondly, you could sum up sales by week. This may remove some important data that you want to be able to see.
Using a moving average, you can both smooth out the daily volatility and allow you to observe the long term trend.
Moving Average Example
Original Doc
Use the AVERAGE() function to get the average sales in the first 7-day period.
You can use COPY + PASTE and edit the rows to get the 7-day average from day 2 to day 9.
Or, you can click the row where you want your 7-day average to end and use CTRL + D.
 
Or, you can click the bottom right corner square and drag down!
PROJECT: Exploring Weather Trends
  • I decided to calculate 10-year moving averages.
I copy pasted the global average temperatures from the global_data table. The average global data started at 1750, whereas the average temperature in Denver, CO, USA started in 1775. There is a big patch of data missing from the data for Denver, CO.
I calculated the MA (10-year) for the Global Average Temperatures.
I created a line graph with y-axis maximum of 10 and minimum of 7.5 with 0.5 intervals. I omitted data for the years that Denver did not have data as this would mean there would be no data to compare for those years.

Leave a Comment