Exponentially Weighted Variance in VBA

[ttbuson]

I have a set of numbers going from oldest to newest in row 1 through 24. I am trying to write a function in VBA to calculate the exponentially weighted volatility of the series of numbers. However, what I have below is not working. Does anyone have any thoughts?

The formula for exponentially weighted variance is on page 6 of this pdf:

http://www.fatih.edu.tr/~jesr/jesr.horasanli.pdf

Thanks!

 Function EWVARIANCE(numbers As Range, Lambda As Single) As Double
Dim xbar As Double
Dim x As Double
Dim c As Range
Dim N As Integer
xbar = WorksheetFunction.Average(numbers)
N = WorksheetFunction.Count(numbers)
For Each c In numbers
    x = x + (Lambda ^ (N - c.Count)) * ((c.Value - xbar) ^ 2)
Next c
EWVARIANCE = (1 - Lambda) * x
End Function

 

[MartinCarlsson]

To make it easier to answer - could you supply a data set - and if possible a solution.