VBA Average if rows are equal-CodePudding

I am trying to do something in VBA, but I am unsure how to go about doing this.

Suppose I have something like the following in Excel:

Group  Rate
1      20%
2      35%
3      31%
3      24%
3      25%
1      15%
3      22%
4      50%
2      50%
2      32%

And I would like to calculate the average per group, resetting each time a new group number is found, i.e:

Group  Rate   Final_Rate
1      20%    20%
2      35%    35%
3      31%    26.67%
3      24%    26.67%
3      25%    26.67%
1      15%    15%
3      22%    22%
4      50%    50%
2      50%    41%
2      32%    41%

In other words, for rows which have the same Group that follow one another directly, I would like to have the average of the rate of each of those rows for the group.

How can I go about doing something like this in VBA?

We can assume that the values for Group and Rate are populated in columns A and B respectively.

I am very much a beginner, I've tried writing some dummy code, but I don't know if I am on the right track.

Sub Calculate_Funny_Average()

    Dim sh As Worksheet
    Dim rw As Range
    Dim RowCount As Integer
    
    
    Set sh = Worksheets("Sheet1")
    
    For Each rw In sh.Rows
    
        If sh.Cells(rw.Row, 1).Value <> "" Then
        
            If sh.Cells(rw.Row, 1).Value = "" Then
                Exit For
            End If
        
            Cur_Group = sh.Cells(rw.Row, 1).Value
            Cur_Rate = sh.Cells(rw.Row, 4)
            
            Next_Group = sh.Cells(rw.Row   1, 1).Value
            
            Do While Cur_Group = Next_Group
                    
                'Something ??
            
            Loop
            
            
        End If
        
    Next rw

End Sub

CodePudding user response：

Option Explicit
Sub test()
Dim arr
Dim i As Integer, m As Integer, x As Integer
arr = Cells(1, 1).CurrentRegion 'catch all data to an array. Make sure there are headers Group, Rate and Final Rate

For i = LBound(arr)   1 To UBound(arr) 'loop all rows, without the header
arr(i, 3) = CDbl(arr(i, 2)) 'the first one must be recorded. cdbl() converts to Double type
x = 1 'x will be the times the group is repeated
If i   x <= UBound(arr) Then 'ifthe row   times the group appears is bigger than number of data rows,quit looping, else
    Do While arr(i, 1) = arr(i   x, 1) 'while the group below is the same as the "i"
        arr(i, 3) = arr(i, 3)   CDbl(arr(i   x, 2)) 'record the value   the value below
        x = x   1 'add  1 to x to see the next row
        If i   x > UBound(arr) Then 'again, if is i   x is bigger than the array, quit
            Exit Do
        End If
    Loop
End If
    For m = x - 1 To 0 Step -1 'now, let's divide. Step-1 is to keep the original number and for the last one being changed
        arr(i   m, 3) = arr(i, 3) / x
        'if you want, record the value with %
        arr(i   m, 3) = Format(arr(i   m, 3), "0.00 %")
    Next m
    
    i = i   x - 1
Next i
Range(Cells(1, 1), Cells(UBound(arr), UBound(arr, 2))) = arr 'print the array over the original range
End Sub

CodePudding user response：

One method of using a formula involves adding a helper column. The helper column will change value when the "Group" changes.

I put the helper column elsewhere on the worksheet, but you can put it where-ever you wish, and/or hide it

Helper Column

    E2: 1
    E3: =IF($A3=A2,E2,E2 1)

and fill down

Final_Rate

C2:  =AVERAGEIFS(Sheet1!$B$2:$B$11,Sheet1!$E$2:$E$11,Sheet1!$E2)

and fill down

If you have Office 365 with the XLOOKUP, you can use the more complicated formula mentioned by @ScottCraner in the comments and avoid the helper column.