exponential moving average formula help, please...

[aiki100]

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!

 

[aiki100]

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 -

 

[offthelip]

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.

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