How to Calculate a Moving Average in Excel
by Shreya Mehta ; Updated September 28, 2017A moving average is a statistic used to analyze parts of a large data set over a period of time. It is commonly used with stock prices, stock returns and economic data such as gross domestic product or consumer price indexes. Using Microsoft Excel, you can organize and calculate moving averages within minutes, allowing you to focus more time on the actual analysis rather than constructing the data series.
Open a new worksheet in Microsoft Excel. Enter dates and their corresponding data points in two columns. For instance, to analyze monthly revenue figures, enter each month in column A and the corresponding revenue figure next to it in column B. A year's worth of data, then, would fill cells A1 through A12 and B1 through B12.
Determine the time interval of the moving average you want to calculate, such as a three-month or six-month moving average. Go to the last value of the first interval and click on the corresponding empty cell to the right. Using the example from Step 1, if you want to calculate a three-month moving average, you would click on cell C3 because B3 contains the last value of the first three months of the year.
Use the AVERAGE function and type a formula into the empty cell you selected, specifying the data range for the first interval. In this example, you would type "=AVERAGE(B1:B3)".
Position your mouse on the lower right corner of the cell with the formula until you see a "+." Left click and drag the formula down to the empty cell next to the last data point in the adjacent column. In the example above, you would drag the formula from cell C3 down to cell C12 to calculate the three-month moving average for the rest of the year.
References
Photo Credits
- photo_camera Stockbyte/Stockbyte/Getty Images