CODEWITHSUNDEEP
arraysvbaexcelloops
Andreas HD
Andreas HD

Reputation: 25

How to loop through columns and calculate averages?

Data seriesdata series

I want to find the average for each segment of ten values down a column. (See data series picture) Continuously all the way to the bottom of the data set. The data set can vary in length, and the code has to be "generic" of sorts.

Based on other code segments I have tried to do this: 

Sub tenthavg()

Dim currentIndex As Long
Dim myArray() As Variant
Dim rng As Range

ReDim myArray(1 To 10)     

Range("b1", Range("b1").End(xlDown)).Select
Set myArray = Selection        
currentIndex = 1

Do Until currentIndex + 1 > UBound(myArray)
    ActiveSheet.Cells(currentIndex, "T") = AverageOfSubArray(myArray, currentIndex, 10)
    currentIndex = currentIndex + 1
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

Unfortunately I can't make it work. Am I approaching this the right way?

If so, what am I doing wrong?

Upvotes: 1

Views: 1037

Answers (2)

MikeD
MikeD

Reputation: 8941

IMHO it's not quite the successful approach ... instead of Selecting EndDown and other concepts borrowed from interactive working make use of VBA's own mechanisms.

A "generic" approch takes Range start address, batch size and offsets where to put the result as arguments ...

Sub AvgX(MyR As Range, S As Integer, ORow As Integer, OCol As Integer)
' MyR = start of range
' S   = batch size
' OCol, ORow = Offsets to place result in relation to last batch value
Dim Idx As Integer, Jdx As Integer, RSum As Variant

    Idx = 1
    RSum = 0
    Do
        For Jdx = 1 To S
            RSum = RSum + MyR(Idx, 1)
            Idx = Idx + 1
            If MyR(Idx, 1) = "" Then Exit Do
        Next Jdx
        MyR(Idx - 1, 1).Offset(ORow, OCol) = RSum / (Jdx - 1)
        RSum = 0
    Loop
End Sub

and is called by

Sub Test()
    AvgX [C4], 10, 0, 1
End Sub

to give you this result ...

enter image description here

Upvotes: 3

Pspl
Pspl

Reputation: 1474

You can get your result in a simpler way:

Sub tenthavg()

Dim LastRow As Long
LastRow = ThisWorkbook.Sheets("Your Sheet Name").Columns(2).Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
Dim myArray(1 To 10) As Double

If LastRow < 10 Then
    MsgBox "There's not enough data!"
Else
    On Error Resume Next
    For x = 1 To LastRow - 9
        For y = 1 To 10
            myArray(y) = ThisWorkbook.Sheets("Your Sheet Name").Cells(y + x - 1, 2).Value
        Next y
        ThisWorkbook.Sheets("Your Sheet Name").Cells(x, 20).FormulaR1C1 = 0
        ThisWorkbook.Sheets("Your Sheet Name").Cells(x, 20).FormulaR1C1 = Application.Average(myArray)
    Next x
End If

End Sub

Please note: I'm assuming you're data starts at B1 and you want the output on column T.

Upvotes: 0

Related Questions