-1

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 stars and bars configurations where the 0's in my sequence represent bars and the 1's represent stars (the final value is a dummy 0 to make things easier to work with). I want the size of each non-empty bucket in a given configuration of stars and bars.

Thanks, in advance.

player0
  • 124,011
  • 12
  • 67
  • 124
cershif
  • 154
  • 1
  • 13
  • Use [tables](https://webapps.stackexchange.com/a/161855/) to show your data structure. If you share spreadsheets, do note that [your email address can be accessed by the public](https://meta.stackoverflow.com/questions/394304/). – TheMaster Jan 01 '22 at 20:11
  • @TheMaster I wonder if it's wise to spread this paranoia of email exposure... it's like saying: "if you go out for a walk you may die in a random car accident". well, both may be true but common... – player0 Jan 01 '22 at 20:25
  • @player0 Disagree. In fact I could easily write a script getting the email addresses of all those who have ever shared a spreadsheet here for the last 4 years or so. It's not uncommon as you believe. I wish to spread awareness until Google changes it's configuration, but also as a side effect, to get well good described questions that don't rely on external links. – TheMaster Jan 01 '22 at 20:32
  • 1
    @player0 I mean... it is clearly a pitfall. I wouldn't share a sheet from my real email address. The metaphor doesn't really hold up. The negative side effect will happen with 100% certainty in this case. The only question is how badly that might affect you. More akin to saying "if you get very drunk, you will have a bad hangover". Most people know this, but still worth telling the uninformed teenager. – cershif Jan 01 '22 at 20:32

3 Answers3

1

try:

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

enter image description here

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)), " ", ))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
1

You could also use the standard method with Frequency which will work with Excel 365 and GS:

=FILTER(FREQUENCY(IF(A1:A11=1,ROW(A1:A11)),IF(A1:A11=0,ROW(A1:A11))),FREQUENCY(IF(A1:A11=1,ROW(A1:A11)),IF(A1:A11=0,ROW(A1:A11))))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

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
TheMaster
  • 45,448
  • 6
  • 62
  • 85