Search code examples
excelvbaexcel-2007

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