Moving Average of values within an array

[Eric Johnson]

What is the best way to calculate moving averages within VBA? I have an
array of values: MyArray(1 to 1000, 1 to 1)
I would like to fill MyOtherArray(1 to 1000, 1 to 1) with 50-value moving
averages of the values in MyArray.
So for x=1 to 49, MyOtherArray(x,1) would be equal to #N/A, because there
are not yet 50 values to average
For x=50, MyOtherArray(50,1) would be equal to the average of the first 50
values of MyArray.

If I were calculating moving averages in a worksheet, I would take the
average of 50 cell ranges using the offset function. What would be a good
way to do this within VBA?

Thanks!

 

[Barb Reinhardt]

SumArray = 0
For i = 1 to 1000
SumArray = myArray(i,1) + SumArray
if i< 50 then
MyOtherArray(i,1) = "N/A"
elseif i > 50 then
SumArray = SumArray - myArray(i-50,1)
MyOtherArray(i,1) = SumArray/50
end if
next i

I think that's what you want. It ain't elegant, but I think it works.

 

[Eric Johnson]

That does the trick. It's a clever way around using the average function.
But I am also interested in calculating rolling standard deviations. Any
ideas as to how to calculate a 50-value rolling standard deviation?