Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of Array : Can use built-in functions?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of Array : Can use built-in functions?
Try something like:
dMin = Application.WorksheetFunction.Min(vData) dMax = Application.WorksheetFunction.Max(vData) On Sep 23, 3:40 am, "Sing" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of Array : Can use built-in functions?
"Randy Harmelink" wrote in message ups.com... 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? On Sep 23, 3:40 am, "Sing" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of Array : Can use built-in functions?
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. On Sep 23, 8:24 am, "Sing" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of Array : Can use built-in functions?
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 "Sing" wrote in message ... "Randy Harmelink" wrote in message ups.com... 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? On Sep 23, 3:40 am, "Sing" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Average of Array : Can use built-in functions?
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 Sing wrote: . . . 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Average of values within an array | Excel Programming | |||
Using Built in Functions in VB Code | Excel Programming | |||
Can I do this with built in functions? | Excel Worksheet Functions | |||
Viewing built-in functions | Excel Programming | |||
Viewing built-in functions | Excel Programming |