Exponential Moving Avg formula in excel with dynamic parameters

[MrSIL]

hi,

Im trying to figure this out myself but incase I cant, I would appreciate some help.
I am trying to calculate the exponential moving avg on a set of financial data but it has to be dynamic whereby I can change the "days" and "smoothing" parameters in a separate table and it will changes the output accordingly which is displayed in a column. The problem is the first calculation is the simple moving avg and then it needs to switch to the EMA calc.

I did a dynamic calc for SMA.
=IF(ROW(D3)>$K$3,AVERAGE(OFFSET(D4,-$K$3,0,$K$3,1)),"")

K3 is the "days" and it gives blank on cells immediately before those days.
Im basically looking for a similar calculation for EMA whereby it either returns a blank if "days" is less than the parameter "DAYS", or it gives the EMA with first cell returning the SMA.

EMA formula is EMA = Closing price x multiplier + EMA (previous day) x (1-multiplier)

multiplier = smoothing factor / ( 1 + days)

If Days is 5 and smoothing in 2, multiplier = 2 ( 1+5) = 0.333

close. EMA
1. 100.
2. 120
3. 130
4 140
5 150. 128 = (first calc is SMA )
6 160. 138.67 = (160*0.333) + (128*0.667)
7 170. 149.11

If I changed the "DAYS" to say 6, it will change the multiplier and calculations will only start from day 6.

Thank you in advance

 

[offthelip]

I have written my own "Exponential moving average" which might help you:

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

B5: =AVERAGE(A1:A5)
b6: =ema($K$3,B5,A6)

You can use the standard excel function average for the 5th value, then use thiis function for the 6th and subsequent.