Exponential Moving Average

[kabe]

I am trying to write a user defined ema function to use on a worksheet. The code I have so far is:

Function mtgEMA(InCell As Range, period As Integer)
ema = 0
ep = 2 / (period + 1)
For i = 1 To period
ema = ema * (1 - ep) + (InCell.Offset(-i + 1, 0) * ep)
Next i
mtgEMA = ema
End Function

This works well enough with a long list of values, but the initial averages at the beginning of the list are not correct. What I really need the function to do is to calculate a Simple Moving Average for the first "Periods" and then use this calculated value as the starting number for calculating the ema. Any assistance appreciated.

~kabe~

[RET79]

I saw an exponential smoothing thing on the Analysis tool pack Add in. I am not suggesting that this will sort out your current query but could be of interest to you.

[dan2]

Hi Kabe

This might help you work it out. It is a user defined function for a rolling average of the last 4 cells in a list that is ever growing and changing.

Function AverLast4(rRange As Range) As Double
Dim Cell As Range
Dim DummyRange As Range

For Each Cell In rRange.Cells
Set DummyRange =Cell.Resize(1,rRange.Cells.Count - _
(Cell.Column - rRange.Column))
If Application.Count(DummyRange) = 4 Then
AverLast4 = Application.Sum(DummyRange) / 4
Exit For
End If
Next Cell

End Function

This wont solve your problem but I hope it might help.

Thanks to Leo for the above function

Dan.

[Jay Petrulis]

Here is a function you might adapt to your needs.

Posted by WoLong in microsoft.public.excel.programming

---------------------------
Public Function EMA(n As Integer, DataCol As String) As Double
Dim K As Double
Dim Diff As Integer
Dim myCell As Range
Set myCell = Application.Caller
Diff = myCell.Column - Columns(DataCol & ":" & DataCol).Column
If myCell.Offset(-1, 0).Value = 0 Then
EMA =
Application.WorksheetFunction.Average(Range(myCell.Offset(0, -Diff),
myCell.Offset(-n + 1, -Diff)))
Else
K = 2 / (n + 1)
EMA = (myCell.Offset(0, -Diff).Value) * K + (myCell.Offset(-1,
0).Value) * (1 - K)
End If
End Function
-------------------------

HTH,
Jay