exponential moving average formula help, please...

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
102
Greetings all -

I am trying to build a formula to calculate an exponential moving average. Have searched both Mr Excel and elsewhere and have gotten several (conflicting) answers. I thought I had gotten this right yesterday, but now that I am validating things, I've found that what I have is for sure incorrect...



Here is what I have so far, based on the research already done, for an 8 period exponential moving average in AE11:

=J11*2/(8+1)+AE10*(1-2/8+1)

where J11 = most recent data point

AE10 = SUM(J2:J9)/8 (the simple Moving Average of the COL J data points)


It IS doing (some) kind of exponential craziness, but clearly not what we are after. If anyone has any thoughts on this please, I would be most appreciative...

Thanks so much!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hello -


For some reason, the above info that I got from previous research was incorrect. I have now found and implemented this below, and I believe it to be accurate for EMA.

Thanks to all who looked -


=([FONT=&quot]J11[/FONT]-[FONT=&quot]AE10[/FONT])*(2/[FONT=&quot]([/FONT]8+1[FONT=&quot])[/FONT])+[FONT=&quot]AE10


=(Close-EMA of previous day) * ([/FONT]
multiplier) + EMA of previous day.


PS- in the very first cell, the "EMA of previous day," can reference a Simple moving average (in this case an 8 period SMA) to start off the calculation.


Thereafter, the formula in the next cell (and all the rest) down will use the EMA.




Hope this helps someone else down the road -





 
Last edited:
Upvote 0
I wrote my own User defined function to do an exponential moving average, if you put it in your personal.xlsb file you can then use it in any workbook.

Code:
Function EMA(Period, Lasttime, Drive)If Not (IsNumeric(Period)) Then
  EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
    TC = 2 / (Period + 1)
    EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,480
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top