How to count the amount of zeros on a line and between two consecutive 1

[Bambynor]

I would like to have some help regarding a formula that I can use to determine the amount of zeros between two consecutive 1.

Consider following example: 34 columns of which seven are 1 and the rest zeroes:
0 1 0 1 0 0 1 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0

1 1 2 8 1 6 1 7

- There is one zero before the first 1.
- There is one zero between the first and the second 1
- There are two zeroes between the second and the third 1
- There are eight zeroes between the third and the fourth 1
.
.
.
There is one zero between the sixth and the seventh 1
- There are seven zeroes after the seventh/last 1

The answer I am looking for is then:
1 1 2 8 1 6 1 7

Imagine that there are over 300 lines (see attached excel file)...it will take ages to do the counting manually...

Is there a formula that I can use to get the result?

Bambynor

[XLent]

do you mean:

AJ1: =TRIM(SUBSTITUTE(TEXTJOIN(" ";;" ";FREQUENCY(IF(NOT(B1:AI1);COLUMN(B1:AI1));IF(B1:AI1;COLUMN(B1:AI1)));" ");" 0";""))
copied down

above would return "1 1 2 8 1 6 1 7"

[Bambynor]

Thank you XLent. Is it possible to get the answer split in eight cells:
AJ - 1
AK - 1
AL- 2
AM - 8
AN - 1
AO - 6
AP - 1
AQ - 7
I ask this because I would like to have statistically the minimum an maximum for each of the eight variables.
Bambynor

[XLent]

so, given O365, you could perhaps use something like:

AJ1: =IFERROR(MID(TEXTJOIN(" ";;TEXT(FREQUENCY(IF(NOT($B2:$AI2);COLUMN($B2:$AI2));IF($B2:$AI2;COLUMN($B2:$AI2)));"[=0]"""";00"));1+3*(COLUMN(B2:AI2)-COLUMN(B2));2)+0;"")
copied down (should spill across)

I've attached a working version of the same in case the above does not translate correctly for your locale (albeit the above is in AL1 rather than AJ1, as AJ1 holds the earlier string example)

[Bambynor]

Wow!
I am impressed...thank you very much for the help.

Bambynor

[Bo_Ry]

Please try

=TRANSPOSE(LEN(FILTERXML("<x><m>"&SUBSTITUTE(CONCAT(1-B1:AI1);0;"</m><m>")&"</m></x>";"//m[.>0]")))

[XLent]

VERY nice @Bo_Ry !

[Bambynor]

There is one more thing:
The formula proposed give following result:

0 1 0 1 0 0 1 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0 -RESULT- 1 1 2 8 1 6 1 7 - CORRECT

1 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 1 1 1 0 1 0 0 0 0 0 0 0 0 0 0 -RESULT- 11 5 1 10
In my opinion the correct result is: 0 0 11 5 0 0 1 10
There are no zeroes before the first 0, there are no zeroes between the first and the second 1 - that explains the 0 0 at the start. Between the fourth, the fifth and the sixth 1 there are no zeroes in which case the result should have two zeroes between 5 and 1.
Any chance to modify the formula (the last one proposed that gives the result split in eight columns)?
Bambynor

[Bambynor]

There is one more thing:
The formula proposed give following result:

0 1 0 1 0 0 1 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0 -RESULT- 1 1 2 8 1 6 1 7 - CORRECT

1 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 1 1 1 0 1 0 0 0 0 0 0 0 0 0 0 -RESULT- 11 5 1 10
In my opinion the correct result is: 0 0 11 5 0 0 1 10
There are no zeroes before the first 0, there are no zeroes between the first and the second 1 - that explains the 0 0 at the start. Between the fourth, the fifth and the sixth 1 there are no zeroes in which case the result should have two zeroes between 5 and 1.

1 0 0 0 1 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 1
The result should be: 0 3 1 6 11 6 0

Any chance to modify the formula (the last one proposed that gives the result split in eight columns)?
Bambynor

[Bo_Ry]

Thanks, Luke.

the correct result is: 0 0 11 5 0 0 1 10

Please try
=TRANSPOSE(LEN(FILTERXML("<x><m>1"&SUBSTITUTE(CONCAT(1-B1:AI1);0;"</m><m>1")&"</m></x>";"//m"))-1)

[Bambynor]

EXCELLENT!!
This is amazing...
Thank you very much.

Bambynor