How to take an average of cells that are split apart consistently in excel?

Question

I would like to take an average of the cell every 7 cells. For example, I want to take the average of Cell A1, A8, A15, A22 etc. Then another average of A2, A9, A16, A23 etc. How can I do this quickly as I have a large data set.

Thanks

Answer 1

Put this array formula in the first cell:

=AVERAGE(INDEX(A:A,N(IF({1},(ROW(A1:INDEX(A:A,MATCH(1E+99,A:A)/7))-1)*7+ROW(1:1)))))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode.

Then copy/drag down 7 cells.

Answer 2

Maybe a user defined function

Public Function Get7Average(ByVal rng As Range) As Variant
    Dim arr(), i, total As Long, counter As Long
    If rng.Columns.Count > 1 Or rng.Cells.Count < 7 Then
        Get7Average = CVErr(xlErrNA)
        Exit Function
    Else
        arr = rng.Value
        For i = LBound(arr, 1) To UBound(arr, 1) Step 7
            counter = counter + 1
            If IsNumeric(arr(i, 1)) Then
                total = total + arr(i, 1)
            End If
        Next
    End If
    If total = 0 Then
        Get7Average = CVErr(xlErrNA)
        Exit Function
    End If
    Get7Average = total / counter
End Function

In sheet