Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of values within an array
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of values within an array
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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of absolute values of moving ranges | Excel Discussion (Misc queries) | |||
Moving average | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Moving average | Charts and Charting in Excel |