How to calculate And Plot Chart Moving Average In Excel

 A moving average is a statistic used to analyze subsets of a large data set. It is often used to analyze stock quotes, stock returns, economic data, such as gross domestic product and consumer price indices. It is time series data on the average of a subset of data to calculate points.The Moving Average analysis tool projects values in the forecast period the average value of the variable based on a number of previous periods. Moving averages provide information about trends, the arithmetic average of all historical data would mask.
Using Microsoft Excel to calculate moving averages for large databases. The data can be easily calculated and organized in a few minutes, allowing the user more time to concentrate on the real analysis instead of creating a series.
Charting the Moving Average
1. On the Tools menu, click Data Analysis.
2. In the Data Analysis dialog box, click Moving Average, and then click OK.
3. The Moving Average dialog box opens.
In the Input Range box, enter a single row or column of data.
In the Interval box, enter the number of values that you want to include in the moving average. In this example, enter 3, the default interval.
Note The interval is the number of data points used to calculate the moving average. The larger the interval, the smoother the moving average line; the smaller the interval, the more the moving average is affected by individual data point fluctuations.
In the Output Range box, enter the cell address where you want the results to start.
Select the Chart Output check box to see a graph comparing the actual and forecasted inventory levels.
Click OK.


Video tutorials










How to do it without Data Analysis Tool Help
First of all for it you have to start a new workbook in Microsoft Excel.
After it enter the date and data point in two columns.
Now you have to think about what type of moving average you want to calculate on the quarterly, six month or yearly basis.
For it you have to carefully select the cells as for six months basis
for example if you have cells B1 through B12 populated with data, you would click on cell C6 since B6 contains the last value of semi annual one.
You have to then Type =AVERAGE(data range),in the formula bar where data range is specified as the range of the first period.
In this example, the formula would appear as =AVERAGE(B1:B6).
Now move your mouse on the lower right hand corner of the cell with the formula until you see a “+”. Left click once and drag the formula down to the last data point.
Sources:
http://office.microsoft.com/en-us/excel/HA010864801033.aspx