Count average value of every 6th column in one row via VBA (Excel)

Question

Right now I'm writing macro on VBA , I almost finished it, but right now i'm stuck on one issue. I can't understand how should I count average value of range that consists of every 6th column of my table. In excel this formula looks like "=AVG(I3, O3, U3...HE3)"

I tried to use macro recorder but it was just hard-coding average value by repeating the whole formula and every cell address. But my table updates everyday and everyday I add 6 new columns at the end of the table, left from last column and last column is column that stores average value of every 6th column of a row. I tried to do it through loop and I think that I'm pretty close to solving this issue, but I don't understand how to add looped values to a particular cell with formula.

Here is my code:

Dim i As Integer
i = 8
For i = 8 To rng.Columns.Count Step 6
rng.Cells(3, rng.Columns.Count) = Application.WorksheetFunction.Average(rng.Cells(3,i))
Next i

rng - is Range variable that stores my table.

With above code I managed to loop through all cells that I need to count avg value, but I don't understand how to get values of these cells to a particular cell that counts avg value. Could you please help me?

Answer 1

You can just sum the values then divide by the count of (columns-8)/6.

Dim i As Integer
Dim sum as long
i = 8
sum = 0
For i = 8 To rng.Columns.Count Step 6
    sum = sum + rng.Cells(3,i).value
Next i

rng.Cells(3, rng.Columns.Count).offset(0,1).value = sum/((rng.columns.count-8)/6) ' I assumed you want the value in rng.columns.count +1, since but change it if you want.

EDIT, can't use rng.columns.count+1 I think since the range ends at the count. Changed it to offset.

Also note that we have no error checking in this code.
I would suggest something like If Isnumber(rng.Cells(3,i).value) then before adding to the sum since a string value will break the code

Answer 2

I also found out an alternative method to do this, it's similar to @Andreas, but I offer to use in-loop counter to get the number of iterated values that are not empty. In my case I need to get average value of range of cells that are separated by 6 columns. I have a big table with a lot of columns and rows and I need that average value for each row, so here is @Andreas modified code:

Sub countAVG()
 
Dim i As Integer               ' row
Dim j As Integer               ' column
Dim sum As Long                ' sum variable that stores sum of all iterated values
Dim numberOfValues As Long     ' variable that stores the number of iterated values (that are not empty)
Dim rng As Range
 
Set rng = Range("B2").CurrentRegion
sum = 0
numberOfValues = 0
i = 3                          ' start from 3rd row
j = 8                          ' and 8th column
For i = 3 To rng.Rows.Count Step 1
    For j = 8 To rng.Columns.Count - 1 Step 6   ' skip 6 columns from every cell
        If IsEmpty(rng.Cells(i, j)) Then        ' if cell is empty then we skip it
        numberOfValues = numberOfValues         ' by leaving our number of cells as it is
        sum = sum                                                                            ‘ same with sum
        Else:
        numberOfValues = numberOfValues + 1     ' otherwise add 1 to a current number of cells values
        sum = sum + rng.Cells(i, j).Value       ' sum not empty cell value with our previous sum
        End If
    Next j
    On Error Resume Next    ' turning off error notifications in case all values are empty (better to use IsEmpty())
    rng.Cells(i, rng.Columns.Count).Offset(0, 1).Value = sum / numberOfValues
    On Error GoTo 0         ' turning back error notifications
    sum = 0
    numberOfValues = 0
Next i
 
End Sub