Moving Average is the simplest indicator for analyzing your data's trend and anticipating trend reversal in Excel. Let's see how to build the formula in this tutorial.
Weather history data of Miami
When you have data with many variations, like temperatures over a year, the moving average helps you hide some daily unexpected results. For instance, here is the chart of the temperature (in Celsius) in New York in 2020.
You can download most of the weather historical data worldwide with this website. When you have reached the page you want, you can easily download the csv file by clicking on this icon (here is the direct link to the file)
Then, after a few modifications of the files, you should have a file like this.
Representation of the data
- When you have daily data like this, the best chart type is a Line Chart.
- When you click on the Recommended chart menu, it's the second choice proposed by Excel.
Curve analysis
The curve generally exhibits lower temperatures from January to April and higher temperatures from May to September. However, significant variations occur within short time intervals.
- For instance, between 04/01/2020 and 09/01/2020, the temperatures have dropped by 12 C° in 6 days.
- But between 09/01/2020 and 12/01/2020, the temperatures have risen by 17 C° in 3 days.
And this is visible on the chart 🫣😱
Build a Moving average with Excel
The best technique is to use the moving average to avoid these important variations and to have a better analysis. So in column C, you write the following formula in C11 (with no $)
=AVERAGE(B2:B11)
And then, you copy-paste this formula to the other cells of your document. Just like that, you have to build a moving average because the cells aren't locked with the $
And when you add this column to your chart, you can visualize a new curve that reflects a trend for 10 days.
And of course, if you build a moving average for 20 days, the curve will mitigate the variations. For instance, between September and December, the moving average of 20 days shows a constant decrease in the temperature.
Bollinger Bands
A good case study for using a moving average is in constructing Bollinger Bands.
