array revisited

[RobcPettit]

Hi, Sorry to ask again. Im asigning values to a vba static array, when
I got 100 values, is there a way to calculate the average of the last
50 values, then if I add further values, Iwant to average only the last
50 added.
Regards Robert

 

[Nigel]

You can determine the average by looping through the Array and computing the
arithmetical mean....

For x = 1 to 50
xSum = xSum + myArray(x)
Next x
xMean = xSum / 50

(above assumes Option Base = 1)

The question posed, to calculate a moving average, depends on the ability to
track the last value in the array. The array has 100 values, any or all of
which could be set to zero. You could scan the array looking for the first
empty value and then use that to compute the mean of the preceding 50
values.

Sub aTest()

Dim x As Integer, LastEntry As Integer
Dim xSum As Double
Dim xSpan As Integer

xSpan = 50 ' sets the number of values to compute the mean over

' determine the last non-empty value in the array
For x = 1 To 100
If IsEmpty(myArray(x)) Then
LastEntry = x - 1
Exit For
End If
Next x

' compute the mean for the last 50 ( xSpan) of values in array
If LastEntry >= xSpan Then
For x = LastEntry To LastEntry - xSpan + 1 Step -1
xSum = xSum + myArray(x)
Next x
MsgBox "Mean: " & xSum / xSpan
Else
MsgBox "Insufficient entries to compute"
End If

End Sub

 

[RobcPettit]

Nigel, Thanks for your reply. Very good. Working on it now. Thanks for
pointing me in right direction.
Regard Robert