I have an array that looks like this

11100100110

essentially, an array of fixed size with each item being a 1 or 0 with the last item always equal to 0.

Consider each set of consecutive 1's to be a "bucket". I'd like a formula to determine the size of each bucket. So the output of this formula for the above sequence should be

312

as an array. Ideally this works in both excel and google sheets.

If you are interested this is the result of a list of

CodePudding user response：

try:

=INDEX((JOIN(, LEN(SPLIT(A1, 0)))))

update:

=INDEX(IFERROR(1/(1/SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IFERROR(1/(1/
 LEN(SPLIT(SUBSTITUTE(FLATTEN(QUERY(
 TRANSPOSE(A1:K),, 9^9)), " ", ), 0))))),, 9^9)), " ", ))))

CodePudding user response：

Assuming A2:A9 contains the data,

=ARRAYFORMULA(QUERY(FREQUENCY(IF(A2:A9,ROW(A2:A9)),IF(NOT(A2:A9),ROW(A2:A9))),"where Col1>0",))

• FREQUENCY(data,classes) to get the frequency of data in classes
• Make sequence of row numbers as data, if 1
• Make sequence of row numbers as classes, if not 1
• QUERY to get rid of zeros