Say I have a vector of 0's and 1's like this:
I would like to find the number of "runs" for a sequence of 1's (in this case "runs" = 2 -- there are two times where 1's turn on and turn off). I would also like to find the max length of the run (in this case, it is 3).
I was able to find the number of runs using by first calculating the difference and then running a SUMIF
(see below), but have not found a succinct way of determining the max length of the run.
For the largest sequence you can use this array formula, so confirm with CTRL, SHIFT and ENTER.
=MAX(FREQUENCY(IF((A1:A10=1)*(A1:A10<>""),ROW(A1:A10)),IF(A1:A10=0,ROW(A1:A10))))