average algorithm

[ersatz]

Hi,
I have a column (field1) with n values.
I need an algorithm to calculate the average for each k values beginning with 1 until n-k+1.
For example I have 10 values and k=4
I need to calculate the average of 1 to 4 values, 2 to 5, 3 to 6, 4 to 7, …..7 to 10.
I w ant to put all this results in a new column (field2) beginning with k position and finishing to the end of column.
For example I want to put the first average in row 4, the second in row 5 and so far.
Can anybody help me, please?

 

[beetee]

' moving average algorithm

Dim AccumCount As Integer
Dim Accum As Double
Dim ValArray() As Double
Dim ValArrayEls As Integer
Dim ValArraySize As Integer
Dim ValArrayOffset As Integer

Sub InitMA()
Erase ValArray
Accum = 0
AccumCount = 10
ValArraySize = 0
End Sub

Function CalcMA(ValNumber As Integer, Value As Double)
Dim RetVal As Double
If ValNumber < AccumCount Then
Accum = Accum + Value
If ValNumber >= ValArraySize Then
ValArraySize = ValArraySize + 16
ReDim Preserve ValArray(ValArraySize)
End If
ValArray(ValNumber) = Value
RetVal = Accum / (ValNumber + 1)
Else
Accum = Accum - ValArray(ValArrayOffset) + Value
ValArray(ValArrayOffset) = Value
ValArrayOffset = ValArrayOffset + 1
If ValArrayOffset >= AccumCount Then ValArrayOffset = 0
RetVal = Accum / AccumCount
End If
CalcMA = RetVal
End Function

Sub TestIt()
InitMA
Dim counter As Integer
Dim CurVal As Double
For counter = 0 To 19
Debug.Print CalcMA(counter, CDbl(counter) + 1)
Next counter
End Sub