CODEWITHSUNDEEP
excel
user2409512
user2409512

Reputation: 25

Excel: maximum of consecutive occurrences of a value in a row

I need a formula to calculate the maximum of consecutive occurrences of a value (value searched is 1) in the same row:

For example, for this row:

1 1 1 0 0 1 1 0 1 1 1 1 0 1 1 0 0 1 0 1 1 1

The formula result should be 4, since the maximum number of consecutive occurrences of the number 1 is 4.

Is it possible?

Many thanks for any help :)

Upvotes: 1

Views: 4056

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

With data in row 1, in A2 enter:

=A1

in B2 enter:

=IF(B1=1,1+A2,0)

and copy across. then in another cell enter:

=MAX(A2:V2)

enter image description here

EDIT#1:

If you wish to avoid the use of a helper column, then consider this UDF():

Public Function Consec1(rng As Range) As Long
    Dim x As Long, y As Long, r As Range
    x = 0
    y = 0
    For Each r In rng
        If r.Value = 1 Then
            x = x + 1
        Else
            If x > y Then
                y = x
            End If
            x = 0
        End If
    Next r
        Consec1 = y
End Function

Upvotes: 3

Related Questions