CODEWITHSUNDEEP
excel
Ian Huang
Ian Huang

Reputation: 1

Find consecutive numbers in Excel

Question: if I have a column of data showing 010110111001111000

1=breach and 0=non-breach

Find the total number of consecutive breaches, for example '11' implies 1 consecutive breaches and '111'implies 2 consecutive breaches

By inspection, total number of consecutive breaches = 6

Is there a formula in Excel that can solve this problem?

Thank you

Upvotes: 0

Views: 536

Answers (2)

Rosetta
Rosetta

Reputation: 2725

Here is another way:

  • 2nd '1' is a breach, so replace the 1st '1' as '0'
  • let the very beginning always as '0'.
  • removing all '0's
  • count the length.

enter image description here

Combining the formula:

=LEN(SUBSTITUTE(REPLACE(SUBSTITUTE(A7,"01","00"),1,1,0),0,""))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152575

Use COUNTIFS with offset ranges:

 =COUNTIFS(A1:A17,1,A2:A18,1)

Note the two ranges are offset one row from each other, so it counts where there are two 1s together.

enter image description here

Upvotes: 2

Related Questions