Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Moving average means we calculate the average of the averages of the data set we have, in excel we have an inbuilt feature for the calculation of moving average which is available in the data analysis tab in the analysis section, it takes an input range and output range with intervals as an output, calculations based on mere formulas in excel to calculate moving average is hard but we have an inbuilt function in excel to do so.
Excel Moving Average (Table of Contents)
- Moving Average in Excel
- Simple Moving Average in Excel
- Simple Moving Average through Data Analysis Tab in Excel
- Weighted Moving Average in Excel
- Exponential Moving Average in Excel
What is Moving Average in Excel
Moving average is a widely used technique in time series analysis that is used to predict the future. The moving averages in a time series are basically constructed by taking averages of various sequential values of another times series data.
There are three types of moving averages namely simple moving average, weighted moving average, and exponential moving average in excel.
#1 – Simple moving average in Excel
A simple moving average helps in calculating the average of the last number of periods of a data series. Suppose prices of n period are given. Then the simple moving average is given as
Simple moving average= [P1+P2+………….+Pn]/n
#2 – Weighted moving average in Excel
The weighted moving average provides the weighted average of the last n periods. The weighting decreases with each data point of the previous time period.
Weighted moving average = (Price * weighting factor) + (Price of previous period * weighting factor-1)
#3 – Exponential moving average in Excel
It is similar to simple moving average that measures trends over a period of time. While simple moving average calculates an average of given data, exponential moving average attaches more weight to the current data.
Exponential moving average =(K x (C – P)) + P
- K = exponential smoothing constant
- C= current price
- P= previous periods exponential moving average (simple moving average used for first periods calculation)
How to Calculate Moving Average in Excel?
Below are the example of moving average in Excel.
Example #1 – Simple Moving Average in Excel
For calculating the simple moving average, we have taken sales data of a company from January to December for the year 2018. Our target is to smooth the data and to know the sales figure in January 2019. We will use three months moving average here.
Step 1: The moving average of January, February, and March is calculated by taking the sum of the sales figure of the months and then divided it by 3.
Step 2: Selecting at the corner of D5 cell and then just dragging and dropping down will give the moving average for the remaining periods. This is excel’s fill tool function.
The sales prediction for January 2019 is 10456.66667.
Step 3: Now we plot the sales figure and moving average in the line graph to understand the difference in trend. This can be done from the insert tab. Firstly we have selected the data series and then from the Charts section under insert, we have used the line graph.
After creating the graphs, it can be seen that the graph with the moving average is much more smoothed out than the original data series.
Example #2 – Simple Moving Average through Data Analysis Tab in Excel
- Under the Data tab under the Analysis group, we have to click Data Analysis. Following is the screenshot.
- From data analysis, the moving average can be accessed.
- After clicking the moving average, we have selected the sales figure as the input range.
- The labels in the first row are clicked in order to make excel understand that the first row has the label name.
- Interval 3 is selected as because we want three years moving average.
- We have selected the output range with adjacency to the sales figure.
- We also want to see the chart output wherein we will be able to see the difference between the actual and forecasted.
This chart shows the difference between actual and Forecasted Moving Average.
Example #3 – Weighted Moving Average in Excel
We use the three years weighted moving average and the formula is given in the screenshot.
After using the formula, we got the moving average for a period.
We got the moving average for all other periods by dragging and dropping values in the following cells.
The forecast for January 2019 i.e. 10718.33
Now we took the line graph to see the smoothening of data. For this, we have selected our month the forecasted data and then inserted a line graph.
Now we will compare our forecasted data with our actual data. In the below screenshots we can easily see the difference between the actual data and forecasted data. The graph on the top is the actual data and the graph in the below is the moving average and forecasted data. We can see that the moving average graph has smoothened significantly as compared to the graph that contains the actual data.
Example #4 – Exponential Moving Average in Excel
The formula for exponential moving average is St=α.Yt-1+(1- α)St-1……(1)
- Yt-1 = actual observation in the t-1th period
- St-1= simple moving average in the t-1th period
- α = smoothening factor and it varies between .1 and .3. Greater the value of α closer is the chart to the actual values and lessen the value of α, more smooth the chart will be.
First, we calculate the simple moving average as shown earlier. After that, we apply the formula given in equation (1). For fixing the α value for all the following values we have pressed F4.
We get the values by dragging and dropping in the following cells.
Now, we want to see the comparison between the actual values, simple moving average, and exponential moving average in excel. We have done this by doing a line chart.
From the above screenshot, we can see the difference between the actual sales figure, simple moving average, and exponential moving average in excel.
Things to Remember About Moving Average in Excel
- The simple moving average can be calculated using an AVERAGE function in excel
- Moving average helps in smoothening the data
- Seasonal averages are often termed as a seasonal index
- The exponential moving average in excel gives more weight to the recent data than the simple moving average. Therefore smoothening in case of the exponential moving average in excel is more than that of the simple moving average.
- In businesses like a stock market, moving average helps the trader to more easily identify the trend.
This has been a guide to Moving Average in Excel. Here we discuss how to calculate 3 types of moving averages in excel (Simple, Weighted and Exponential) along with practical examples and downloadable excel template. You may learn more about excel from the following articles –