Calculate Moving Average in Excel

Calculate Moving Average in Excel
Last Updated on 04/11/2023
Reading time: 3 minutes

Moving Average is the simplest indicator to analyze the trend of your data and also to anticipate trend reversal.

  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 a lot of variations, like the temperatures over a year, the moving average would help you to 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 in the world 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)

Website to download historical weather data

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 type of chart 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

Even if the general shape of the curve presents low temperature between January till April, and high temperature between May and September, you can see there is a lot of variations in a short period of time.

  • 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

To avoid these important variations and to have a better analysis, the best technique is to use the moving average. 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 04/11/2023

Moving Average is the simplest indicator to analyze the trend of your data and also to anticipate trend reversal.

  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 a lot of variations, like the temperatures over a year, the moving average would help you to 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 in the world 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)

Website to download historical weather data

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 type of chart 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

Even if the general shape of the curve presents low temperature between January till April, and high temperature between May and September, you can see there is a lot of variations in a short period of time.

  • 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

To avoid these important variations and to have a better analysis, the best technique is to use the moving average. 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 *