Forum Discussion

sirwazzles's avatar
sirwazzles
Copper Contributor
Feb 18, 2023
Solved

Most # of repeats/duplicates back to back

If I have a column of cells, each cell having a 0 or a 1, how do I count the most number of repeats “back to back”? I want to know the most # of times the number 1 occurs back to back.   ex. 0,1,0...
BI & Data Analysis
excel
Excel on Mac
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 18, 2023

    sirwazzles 

    Another way could be

    =MAX( FREQUENCY( ROW(A1:A15), (A1:A15 <> 1)*ROW(A1:A15)) ) -1
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 18, 2023

sirwazzles 

As variant

=INDEX(
    REDUCE(
        {0,0},
        A1:A15,
        LAMBDA(a,v,
            IF( v = 0,
                a * {0,1},
                IF(INDEX(a, , 1) < INDEX(a, , 2),
                    a + {1,0},
                    a + {1,1})
            )
        )
    ), , 2 )

Resources