Exponential Moving Average

kabe

New Member
Joined
Mar 28, 2002
Messages
3
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~
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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