**Contents**:

What is a Moving Average? How to Calculate it by Hand.

Moving Average in Excel:

Data Analysis Add-In.

Using Functions (Non Data Analysis Option)

## What is a Moving Average?

Watch the video or read the article below:

A moving average is a technique to get an overall idea of the trends in a data set; it is an average of any subset of numbers. The moving average is extremely useful for **forecasting long-term trends**. You can calculate it for any period of time. For example, if you have sales data for a twenty-year period, you can calculate a five-year moving average, a four-year moving average, a three-year moving average and so on. Stock market analysts will often use a 50 or 200 day moving average to help them see trends in the stock market and (hopefully) forecast where the stocks are headed.

An average represents the “middling” value of a set of numbers. The moving average is exactly the same, but **the average is calculated several times for several subsets of data.** For example, if you want a two-year moving average for a data set from 2000, 2001, 2002 and 2003 you would find averages for the subsets 2000/2001, 2001/2002 and 2002/2003. Moving averages are usually plotted and are best *visualized*.

### Calculating a 5-Year Moving Average Example

**Sample Problem:**Calculate a five-year moving average from the following data set:

Year | Sales ($M) |

2003 | 4 |

2004 | 6 |

2005 | 5 |

2006 | 8 |

2007 | 9 |

2008 | 5 |

2009 | 4 |

2010 | 3 |

2011 | 7 |

2012 | 8 |

The mean (average) sales for the first five years (2003-2007) is calculated by finding the mean from the first five years (i.e. adding the five sales totals and dividing by 5). This gives you the **moving average for 2005 (the center year)** = 6.4M:

Year | Sales ($M) |

2003 | 4 |

2004 | 6 |

2005 | 5 |

2006 | 8 |

2007 | 9 |

(4M + 6M + 5M + 8M + 9M) / 5 = 6.4M

The average sales for the **second subset of five years (2004 – 2008)**, centered around 2006, is **6.6M**:

(6M + 5M + 8M + 9M + 5M) / 5 = 6.6M

The average sales for the **third subset of five years (2005 – 2009)**, centered around 2007, is **6.6M**:

(5M + 8M + 9M + 5M + 4M) / 5 = 6.2M

Continue calculating each five-year average, until you reach the end of the set (2009-2013). This gives you a series of points (averages) that you can use to plot a chart of moving averages. The following Excel table shows you the moving averages calculated for 2003-2012 along with a scatter plot of the data:

## Moving Average in Excel 2013: Data Analysis Add-In.

Watch the video or read the steps below:

Excel has a powerful add-in, the Data Analysis Toolpak (how to load the Data Analysis Toolpak) that gives you many extra options, including an automated moving average function. The function not only calculates the moving average for you, it also graphs the original data *at the same time*, saving you quite a lot of keystrokes.

### Excel 2013: Steps

Step 1: Click the “Data” tab and then click “Data Analysis.”

Step 2: Click “Moving average” and then click “OK.”

Step 3: Click the “Input Range” box and then select your data. If you include column headers, make sure you check the Labels in first Row box.

Step 4: Type an interval into the box. An interval is how many prior points you want Excel to use to calculate the moving average. For example, “5” would use the previous 5 data points to calculate the average for each subsequent point. The lower the interval, the closer your moving average is to your original data set.

Step 5: Click in the “Output Range” box and select an area on the worksheet where you want the result to appear. Or, click the “New worksheet” radio button.

Step 6: Check the “Chart Output” box if you want to see a chart of your data set (if you forget to do this, you can always go back and add it or choose a chart from the “Insert” tab.”

Step 7: Press “OK.” Excel will return the results in the area you specified in Step 6.

## Moving Average in Excel: Using Functions (Non Data Analysis Option)

Watch the video, or read the steps below:

## Steps

**Sample problem:** Calculate the three-year moving average in Excel for the following sales data: 2003($33M), 2004($22M), 2005($36M), 2006($34M), 2007($43M), 2008($39M), 2009($41M), 2010($36M), 2011($45M), 2012($56M), 2013($64M).

Step 1: **Type your data into two columns in Excel**. The first column should have the year and the second column the quantitative data (in this example problem, the sales figures). Make sure there are no blank rows in your cell data.

Step 2: **Calculate the first three-year average (2003-2005)** for the data. For this sample problem, type “=(B2+B3+B4)/3” into cell D3.

Step 3: **Drag the square in the bottom right corner down to move the formula to all cells** in the column. This calculates averages for successive years (e.g. 2004-2006, 2005-2007).

Step 4: (Optional)** Create a graph.** Select all of the data in the worksheet. Click the “Insert” tab, then click “Scatter,” then click “Scatter with smooth lines and markers.” A graph of your moving average will appear on the worksheet.

*That’s it!*

Check out our YouTube channel for more stats help and tips!

If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.

Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!
This is perfect and simple to assimilate.

Thanks for the work

This is very clear and informative.

Question: How does one calculate a 4-year moving average? What year would the 4-year moving average center on?

It would center on the end of the second year (i.e. Dec 31st).

Can i you use mean income to forecast future earnings?

Yes :)

Very clear and simple. Thanks a lot

How to create a moving average method? Please guide me.

Do you mean a moving average inventory method?

Where will I center my first forecast for a 2 period SMA? Should I put it on the second or third line?

I would put it on the second line.

i like this it is helpfull

Very good Example

What happen if the total no ofvyear is even?

See my comment above on 4 year moving average…it would center on the end of a year.

anybody knows about centered mean? please kindly tell me if anyone know.

Here it’s given that we have to consider 5 years for getting the mean which is in center.Then what about the rest years if we want to get the mean of 2011…as we don’t have further values after 2012,then how would we calculate it?

As you don’t have any more info it would be impossible to calculate the 5 year MA for 2011. You could get a two year moving average though.

Hi, Thanks for the video. However, one thing is unclear. How to do a forecast for the coming months?

The video shows forecast for the months for which data is already available.

Hi, Raw,

I’m working on expanding the article to include forecasting. The process is a little more complicated than using past data though. Take a look at this Duke University article, which explains it in depth.

Regards,

Stephanie

thank you for a clear explanantion.

Hi

Unable to locate the link to the suggested Duke University article. Request to post the link again

I think this was the one:

https://people.duke.edu/~rnau/Notes_on_forecasting_with_moving_averages–Robert_Nau.pdf

Hi, what if we are to find a 5 week moving average, when we are given a question on yearly basis e.g 2001, 2002, 2003 ……….., 2009

You would have to have the weekly totals.

How can I program the data given en output the moving average for let say four years

Could you clarify what you mean by “program the data given”?

How can I calculate latest year trend variation if Eg; it is 99 or any number

when we going on an equation of {PREVIOUS YEAR – CURRENT YEAR = 123…

123… / PREVIOUS YEAR = 321

321 / 100 = 0.123}

At last, what should we do in the last year ….?

55 ok

65 ok

71 ok

85 ok

99…..?

Somebody, please help me………….

Still weighting to fill this blank column…

Dear all

Hello,

I am working on a major project which is a Modern Technique for long term Load forecasting: Case study Sudan National Grid (Generation of Electricity),and I used some statistical methods such as time series analysis and the average moving average to the middle of the moving average. I need books and references to help me to complete my research.(betalbasheer23@gmail.com)