Jonayed
We will cover the specifics of the Displaced Moving Average (DMA) in the Excel formula for this topic. DMA is a variant of the Simple Moving Average (SMA). So, while dealing with Displaced Moving Average, we must first catch SMA in order to obtain a wider view. The Simple Moving Average (SMA) is essentially a set of averages for several groups within the same dataset. SMA can also be referred to as a moving average or sliding average.
In the above dataset, the Simple Moving Average has been calculated for the Profit(US Dollar) giving the interval of 3 years.
📂 Download Excel File
Download free workbook from here:
What is Moving Average?
With a Moving Average, the median follows the same time frame but moves because of including the newer data.
For example, you must supply the revenue from Days 1, 2, and 3 if someone asks you for the moving average of gross revenue on Day 3. And if someone asks you for the day 4 moving average of revenue, you must respond with the day 2, day 3, and day 4 sales values. You must maintain an identical time period (3 days) as further data is included and construct the moving average using the additional data.
To make it simple to spot patterns, a moving average straightens out any abnormalities (maxima and minima) in the data. Because more data points are contained in each providing multiple when the interval period is larger, there is a greater softening of variations.
Definition of Displaced Moving Average
When moving A Simple Moving Average forward or backward in time to obtain a better prediction trend is called a Displaced Moving Average (DMA). DMA might be advantageous or detrimental. Positive displacement implies that all average values have been moved ahead in time, and negative displacement has been moved backward in time. In the examination of meteorological data and equity markets data, we simply employ the moving averages. In addition, DMAs are better than SMAs for leading indicator purposes.
Learn to Calculate Displaced Moving Average Applying Formula in Excel with 2 Easy Methods
While writing this article, “How to calculate Displaced Moving Average Formula in Excel”, we’ve considered a dataset that contains approximately 4 columns and 17 rows. In this dataset, we added Years, Profit (US Dollar), and Simple Moving Average to make you better understand. But if you want, you can change the entities and put your own values.
1. Using AVERAGE Function and Inserting Chart to Calculate Simple Moving Average
If you want to find the Displaced Moving Average, you have to calculate the Simple Moving Average first. There is a procedure to calculate Simple Moving Average by using the AVERAGE function in Excel. After applying the function, you need to insert the chart. This technique is very handy and simple. You can do this by maintaining the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D8.
- Secondly, put the following formula on that cell.
=AVERAGE(C6:C8)
- Afterward, press Enter button.
- Belatedly, use the fill handle to drag the formula to the entire column.
- Now if you press any particular cell, you will notice that the moving average is changing according to the cell.
- This particular change is known as the Displaced Moving Average.
- After that, go to the Insert tab and click on the Recommended Charts.
- Now you will find a dialogue box name Insert Chart.
- Seventhly, select any chart you want from the Recommended Charts.
- Then tap the OK button.
- Finally, you will get the desired result in the chart.
Undoubtedly, this is a simple process to calculate Displaced Moving Average using the formula in Excel.
📕 Read More: 2 Examples to Calculate Centered Moving Average in Excel
2. Applying Data Analysis Tool in Excel
The Data Analysis tool is one of the easiest ways to calculate the Displaced Moving Average using the formula in Excel. To do this you have to activate the Data Analysis tool first. It is a very simple procedure. You can do this very easily if you follow the given steps carefully.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you need to go to the File tab.
- Secondly, click on the Option.
- Then you will get a dialogue box name Excel Options.
- Thirdly, select the Add-ins.
- Now select the Excel Add-ins to Manage and click on Go.
- Fourthly, select the Analysis ToolPak from Add-ins.
- After that, tap on the OK button.
- Afterward, return to your worksheet.
- Now go to the Data tab and select Data Analysis.
- Again you will get a dialogue box named Data Analysis.
- Belatedly, select the Moving Average and click on the OK button.
- Then you will get another dialogue box named Moving Average.
- Give the Input Range:
$C$6:$C$17
- After that, select your desired Interval and select the Output Range:
$D$6:$D$17
- Afterward, check the Chart Output, and finally, tap the OK button.
- Eventually, you will get the result that you need.
Truly, this is a very simple and handy approach to calculating Displaced Moving Average using the formula in Excel.
📕 Read More: 5 Steps to Estimate Triple Exponential Moving Average in Excel
Calculate 7 Day Moving Average in Excel
If you want to calculate a 7-day or 3-month, or 12-month moving average in Excel then this portion is for you. We will do exactly the thing that you want to have. In this case, we will use the SUM function in Excel. This approach is not tough. You can easily do this if you carefully follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, you need to select cell E6.
- Secondly, put the following formula on that cell.
=SUM(C6:C12)/7
- Finally, click on the OK button.
- Eventually, you will get the result as well.
Indeed, you can easily achieve this procedure to calculate the 7-day or 3-month, or 12-month moving average in Excel.
📕 Read More: 5 Ways to Calculate 7-Day Moving Average in Excel
📄 Important Notes
You should be aware of the following things while performing the processes mentioned above:
🖊️ You need to be careful while applying the Excel function.
🖊️ You should be conscious when you intend to use the Data Analysis tool in Excel.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Initially, we gave the definitions of Moving Average and Displaced Moving Average.
📌 Secondly, we calculated Displaced Moving Average using the AVERAGE formula in Excel.
📌 Thirdly, we applied the Data Analysis tool in Excel.
📌 Finally, we tried to calculate the 7-day or 3-month, or 12-month moving average using the SUM function in Excel.
Conclusion
We wish that you would be able to calculate the displaced moving average in Excel formula using this article. As we have shown several methods here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
Related Articles
- 7 Ways to Calculate Average Attendance with Formula in Excel
- 6 Ways to Solve When Average Formula is Not Working in Excel
- 2 Easy Ways to Compute Average Excluding 0 in Excel
- 3 Approaches to Calculate Average of Text in Excel
- How to Calculate Average Percentage Change in Excel
- 4 Ways to Calculate Average, Minimum, and Maximum in Excel
- 7 Easy Ways to Calculate Average Rating in Excel
