Calculate Displaced Moving Average with Formula in Excel

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.

Overview image to calculate Displaced Moving Average using the formula in Excel

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.

Sample dataset containing the record of yearly profit

APPROACH

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.

Using the AVERAGE function to calculate the simple moving average

  • 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.

The result of using the AVERAGE function to calculate the simple moving average

  • After that, go to the Insert tab and click on the Recommended Charts.

Selecting Recommended Charts from the Insert tab

  • 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.

Selecting the desired chart before clicking OK

  • Finally, you will get the desired result in the chart.

Simple moving average with a 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

APPROACH

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.

Options from the File tab

  • 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.

Add-ins from the Excel Options

  • Fourthly, select the Analysis ToolPak from Add-ins.
  • After that, tap on the OK button.

Selecting the Analysis ToolPak from the Add-ins

  • Afterward, return to your worksheet.
  • Now go to the Data tab and select Data Analysis.

Choosing the Data Analysis from the Data tab

  • Again you will get a dialogue box named Data Analysis.
  • Belatedly, select the Moving Average and click on the OK button.

Choosing the Moving Average from the Data Analysis box

  • 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.

Giving the necessary data to calculate the Moving Average

  • Eventually, you will get the result that you need.

The Moving Average is shown with the help of a chart

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.

Using the SUM function to calculate the 7-day moving average

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

(Visited 51 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo