PDA

View Full Version : Average of n elements in an array



bdl004
07-15-2010, 06:11 AM
Hi Everybody,

I am having a problem averaging certain sections of an array in VBA. What I have is an array of around 50,000 numerical entries. I need to take the average of the first ten, second ten, third ten... etc. and store this number in a new array. I would prefer to do this without using too many nested loops however I won't disregard this option. Any thoughts on how to approach this?

Thanks a lot, I appreciate any and all help!

Bob Phillips
07-15-2010, 06:52 AM
StartPos = 11
NumValues = 10

ReDim aryResults(1 To NumValues)
For i = StartPos To StartPos + NumValues - 1

aryResults(i - StartPos + 1) = aryNumbers(i)
Next i

mikerickson
07-15-2010, 07:50 AM
Sub foo()
Dim currentIndex as Long
' get someArray
currentIndex = 1

Do Until currentIndex + 10 > UBound(someArray)
MsgBox AverageOfSubArray(someArray, currentIndex, 10)
currentIndex = currentIndex + 10
Loop

End Sub

Function AverageOfSubArray(myArray as Variant, startIndex as Long, elementCount as Long) as Double
Dim runningTotal as Double
Dim i as Long

For i = startIndex to (startIndex + elementCount -1)
runningTotal = runningTotal + Val(myArray(i))
Next i
AverageOfSubArray = runningTotal / elementCount
End Function

bdl004
07-15-2010, 07:53 AM
Thanks!

Bob Phillips
07-15-2010, 07:56 AM
I forgot one line in my code, namely how to get the average



MsgBox Application.Average(aryResults)

bdl004
07-15-2010, 08:09 AM
Haha I figured that something was missing but it works great now. Thanks again guys!