Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Exponential Moving Average

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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~

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •