Calculate Moving Average in Excel

Calculate Moving Average in Excel
Last Updated on 20/04/2024
Reading time: 3 minutes

Moving Average is the simplest indicator for analyzing your data's trend and anticipating trend reversal in Excel.

  1. Function AVERAGE

    Just apply the AVERAGE function for 10, 20, 50 values

  2. Keep the relative reference

    The trick is to unblock the references of your average data

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.

Daily New Yorks temperature 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.

New Yorks temperature 2020

Representation of the data

  1. When you have daily data like this, the best chart type is a Line Chart.
  2. When you click on the Recommended chart menu, it's the second choice proposed by Excel.
Recommended chart Line Chart

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.
Important temperature variation between 2 dates

And this is visible on the chart 🫣😱

High variation between temperature

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 $

Moving average for the last 10 days

And when you add this column to your chart, you can visualize a new curve that reflects a trend for 10 days.

Chart moving average 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.

Chart moving average 20 days

Leave a Reply

Your email address will not be published. Required fields are marked *

Calculate Moving Average in Excel

Reading time: 3 minutes
Last Updated on 20/04/2024

Moving Average is the simplest indicator for analyzing your data's trend and anticipating trend reversal in Excel.

  1. Function AVERAGE

    Just apply the AVERAGE function for 10, 20, 50 values

  2. Keep the relative reference

    The trick is to unblock the references of your average data

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.

Daily New Yorks temperature 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.

New Yorks temperature 2020

Representation of the data

  1. When you have daily data like this, the best chart type is a Line Chart.
  2. When you click on the Recommended chart menu, it's the second choice proposed by Excel.
Recommended chart Line Chart

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.
Important temperature variation between 2 dates

And this is visible on the chart 🫣😱

High variation between temperature

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 $

Moving average for the last 10 days

And when you add this column to your chart, you can visualize a new curve that reflects a trend for 10 days.

Chart moving average 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.

Chart moving average 20 days

Leave a Reply

Your email address will not be published. Required fields are marked *