Average number of consecutive 0s

[sarwar8]

I have cells in the C column. Many of the cells have values, while others have interspersed "0"s. I want to know the average number of consecutive zeros. For example: 1,0,0,0,0,5,0,0,6 should give me an average of 3.


I am also interested in finding the average number of consecutive 0s, only if there are 3 or more 0s in a row. With the above example, the two 0s in a row wouldn't count, and the average would be 4.

Thank you!

 

[Aladin Akyurek]

1. Control+shift+enter, not just enter:

=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(A2:A10),IF(A2:A10=0,ROW(A2:A10))),IF(1-(A2:A10=0),ROW(A2:A10))),FREQUENCY(IF(ISNUMBER(A2:A10),IF(A2:A10=0,ROW(A2:A10))),IF(1-(A2:A10=0),ROW(A2:A10)))))

2. Control+shift+enter, not just enter:

=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(A2:A10),IF(A2:A10=0,ROW(A2:A10))),IF(1-(A2:A10=0),ROW(A2:A10)))>=3,FREQUENCY(IF(ISNUMBER(A2:A10),IF(A2:A10=0,ROW(A2:A10))),IF(1-(A2:A10=0),ROW(A2:A10)))))

 

[MrIfOnly]

Here's a VBA option:

Private Sub CommandButton1_Click()
Dim r, check, i, count As Integer
Dim zeros() As Integer


r = Worksheets(1).Range("C:C").End(xlDown).Row
For check = 1 To r
    If Cells(check, 3).Value = 0 Then
        ReDim Preserve zeros(i)
        count = count + 1
        zeros(i) = count
    ElseIf count > 0 Then
        i = i + 1
        count = 0
    End If
Next check


Range("D1").Value = Application.WorksheetFunction.Average(zeros)
End Sub

Regards,

CJ