Count cells with certain values which appear between cells with other values

Question

I have one row in Excel with a number pattern (each number is in a different cell) like this.

1 0 0 0 2 0 0 1 0 0 0 0 0 3 0 0 0 0 0 0 0 0 2 0 0 0 1

(My row has 300 cells).

Definition:

• I call a sequence of zeros between non-zero values "0 sequence".
• I call the non-zero value which marks the start and end of a 0 sequence the "mark value"

I want to calculate:

• Count for 0 sequence the number of 0. In this example above, the result is: 3, 2, 5, 8, 3
• Determine the smallest 0 sequence. In the example above, it is 2
• Determine the largest 0 sequence. In the example above, it is 8

• Determine the frequency of each 0 sequence. In the example above, it is:

  • Number of 0 sequence for the entire row with length 1: 0

  • Number of 0 sequence for the entire row with length 2: 1

  • Number of 0 sequence for the entire row with length 3: 2

  • Number of 0 sequence for the entire row with length 4: 0

  • Number of 0 sequence for the entire row with length 5: 1

  • Number of 0 sequence for the entire row with length 6: 0

  • Number of 0 sequence for the entire row with length 7: 0

  • Number of 0 sequence for the entire row with length 8: 1

I tried different Excel formulas like this one:

=ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))-1

This formula does not work for mark values ("D13", "D14") with the same value. It also does not calculate it for more than one 0 sequence.

Any hint, preferably without VBA, would be appreciated.

Answer 1

Assuming your values are in A1:AA1 and outer values are <> 0:

The core of getting the right frequencies:

=FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1)))

This would evaluate to an array of numbers > 3,2,5,8,3

• Count for sequence (returned to B3 in my example):

  =TEXTJOIN(",",1,FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1)))
  

• Smallest sequence (returned in B4):

  =MIN(FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1))))
  

• Largest sequence (returned to B5):

  =MAX(FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1))))
  

• Count of each frequency (returned to B5:B13):

  =COUNT(FILTERXML("<t><s>"&SUBSTITUTE(B$3,",","</s><s>")&"</s></t>","//s[.='"&ROW(A1)&"']"))
  

Or if you have not chosen to use TEXTJOIN for B3:

=SUM(IF(FREQUENCY(IF(B$1:Z$1=0,COLUMN(B$1:Z$1)),IF(B$1:Z$1=0,"",COLUMN(B$1:Z$1)))=ROW(A1),1,0))

Drag down...

Note: All these formulas are array entered through CtrlShiftEnter

---

As you can see I gave your query a little bit of my own twist through how I set up the returned values.

If you are willing to wait a while I'm sure someone can come up with something even simpler (maybe with Excel O365 and its DA-Functions)