[Solved] Return max consecutive events in a column?

[Veda]

Hello, my question is how to write (if possible) a formula/function to return the max times for an unbroken chain of events in a column. For example finding the longest run of consecutive "0"s or "1"s.

Please see the attached snapshot example. To clarify, the answers would be "7" for the max "0"s and "5" for the max "1"s.

Thank you

PS: I'm not that good at spreadsheets so hopefully a straight formula will work please, no tables or other complicated (for me) methods. Maybe it's not possible with a simple formula but worth an ask

[FJCC]

The attached file shows one way to do this with a couple of helper columns. The formulas in columns B and C rely on B1 and C1 containing a zero or being blank.

[Veda]

The attached file shows one way to do this with a couple of helper columns. The formulas in columns B and C rely on B1 and C1 containing a zero or being blank.

Ah, I see what you've done, that's clever. I'm quite used to using helper columns as my spreadsheet skill is quit low. but I didn't see that solution.

Thank you very much, most grateful. I've marked this as solved.

[RusselB]

No matter your spreadsheet skill level, helper columns are, almost, essential.
If you have helper columns to break down a complex formula into simpler steps, it's easier to figure what, if anything, went wrong.
Once you have the formula in simple steps, then you can combine those steps to make, eventually, a single formula.

[Veda]

No matter your spreadsheet skill level, helper columns are, almost, essential.
If you have helper columns to break down a complex formula into simpler steps, it's easier to figure what, if anything, went wrong.
Once you have the formula in simple steps, then you can combine those steps to make, eventually, a single formula.

Yes I agree and using helper columns is how I make many of my formulas to achieve an end result. Still, it's also true that there are much more sophisticated and time saving ways of querying data, such as a data base or even in a spread sheet as you definitely know about, I Just don't know how to do it. Either way I'm happy that a simple solution was given that is within my capabilities so I can move forward.

PS: My business partner is a programmer and he's offered many times to put all my work on a data base, but I keep refusing because I want to think I can do something myself that works. Probably at some point I will have to let him do it because there are things I just don't understand that would make the results so much better, like word queries in strings of text. I was shown the code how to do that but my eyes just glazed over, lol.