Moving Average Using Excel

Stesp to calculate the moving average using Excel are presented.
Note that you need to first load the analysis toolPack in Excel if it is not loaded yet.

Example to be used
Given the stock price of a company over a 12 day period
102.6 , 103.1 , 100.4 , 105.7 , 100.2 , 105.4 , 107.2 , 102.6 , 108.5 , 110.4 , 106.2 , 108.2,
find the 5-day moving average of the stock price and graph both the stock price and the 5-day moving average on the same system of axes.

Step 1 - Organize the data sets in a column (or row) and use labels in the first row such as "Stock Price", as shown below.
Organize Data Into Column
Step 2 - Create a label for the moving average outputs in a column adjacent to the given data.

Create a Label for Moving Average
Step 3 - Press on the "Data" tab and click on "Data Analysis".

Press Data Tab and Click on Data Analysis
Step 4 - Select "Moving Average" and press "OK".

Select Moving Average and Press OK
Step 5 - Clear all inputs and output areas.

Clear Input and Output Areas
Step 6 - Click inside "Input Range" and use the mousse to select the column containing the data values, whose moving average you need to calculate, including the label "Stock Price".

Select Input Range Data
Step 7 - Check labels because you included the label when selecting the input data in step 6.

Check Labels
Step 8 - Input the interval of the moving average to be calculated. In this example we are looking for the 5-day moving average, therefore the interval is 5

Input Interval of Moving Average
Step 9 - Click inside "Output Range" and use mousse to select the first cell below the label "Moving Average".

Select Output Range
Step 10 - Check "Chart Output" and press OK.

Check Chart Output and Click OK
Step 11 - Read and interpret the data and the graphs. The moving average is in column "Moving Average" and start on day 5. You also have the graphs of both the stock prices and the 5-day moving average on the same system of axes which you can format the way you wish.

Output Range

More References and links

  1. Data Analysis in Excel
  2. Load The Analysis ToolPack in Excel
  3. Correlation Coefficient Examples with Solutions
  4. Correlation Problems with Real Life Data