[SOLVED] Count average value of every 6th column in one row via Excel VBA

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

How can 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 the macro recorder but it was hard-coding average value by repeating the whole formula and every cell address. But my table updates every day and 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.

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.

I managed to loop through all cells that I need to count average value, but I don't understand how to get values of these cells to a particular cell that counts average value.

Solution

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