Moving Average of Array : Can use built-in functions?

[Sing]

Dear Excel Gurus,

I would like to do some mathematical operation on an integer array (for
example, finding the maximum or moving average). While it is easy to write
functions to do that, I was wondering if it is possible to make use of the
built-in functions that Excel offers.

Can the built-in functions be made to operate on an integer array?

Thank you for your advice.

 

[Randy Harmelink]

Try something like:

dMin = Application.WorksheetFunction.Min(vData)
dMax = Application.WorksheetFunction.Max(vData)

 

[Sing]

Try something like:

dMin = Application.WorksheetFunction.Min(vData)
dMax = Application.WorksheetFunction.Max(vData)

Thank you for your reply.
There is some complication in my case.

Say I only want to find the minimum of the last 10 elements in vData. Is
Excel flexible enough to specify the start and end element of the array in
which to find the minimum?

 

[Randy Harmelink]

EXCEL can do that with a Range of data, but I don't think VBA can do
it with an Array of data.

I think you would have to copy the data to a new array, then run the
function on that array. But a single function to extract a portion of
an array might be easier than writing your own functions to do each of
the min/max/sum/whatever processing you want to do.

 

[RB Smissaert]

It should be simple to write your own custom function
that does exactly what you want, something along these lines
(no error handling added):

Function GetAverageOfArray(arrLong() As Long, _
Optional lStart As Long = -1, _
Optional lEnd As Long = -1, _
Optional lFirstX As Long = -1, _
Optional lLastX As Long = -1) As Double

Dim i As Long
Dim lSum As Long
Dim LB As Long
Dim UB As Long

LB = LBound(arrLong)
UB = UBound(arrLong)

If lStart = -1 And lEnd = -1 And _
lFirstX = -1 And lLastX = -1 Then
lStart = LB
lEnd = UB
Else
If lFirstX > -1 Then
lStart = LB
lEnd = (LB + lFirstX) - 1
Else
If lLastX > -1 Then
lStart = (UB - lLastX) + 1
lEnd = UB
End If
End If
End If

For i = lStart To lEnd
lSum = lSum + arrLong(i)
Next i

GetAverageOfArray = lSum / (1 + lEnd - lStart)

End Function


Sub test()

Dim i As Long
Dim arr(1 To 100) As Long

For i = 1 To 100
arr(i) = i
Next i

MsgBox GetAverageOfArray(arr)

End Sub


RBS

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
SubArray function can be used for this purpose. E.g.,

Application.Max(SubArray(vData,2,4,1,5)) will return the maximum from
the values in the subarray comprised of Columns 2 thru 4, Rows 1 thru 5
of vData.

The SubArray function can be used this way to specify any contiguous
"range" of data in a 1-D or 2-D array.

Alan Beban