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 

Another way could be

=MAX( FREQUENCY( ROW(A1:A15), (A1:A15 <> 1)*ROW(A1:A15)) ) -1
  • sirwazzles's avatar
    sirwazzles
    Copper Contributor
    Mar 04, 2023

    SergeiBaklan Thanks for the response! I was able to use this code.

Resources