In a spreadsheet (GDocs or similar), how do I find

Question

This seemed like a trivial question to me, but I cannot get it done correctly. Part of my dataset looks like this

1, 1, 1, 0, 0, 1, 1, 1, 1, 1, 0, 0, 0    

and contains two “runs” of 1 (not sure if that’s the correct word), one with a length 3, the other with a length of 5.

How can I use Google Docs or similar spreadsheet applications to find the longest of those runs?

Answer 1

EDIT: whuber's suggestion is just too simple for me to not update this response. One can just use a simple IF statement checking if the current row is equal to 1. If it is, it starts a counter (the prior row + 1), if it is not it starts the counter again at 0.

You just need to initialize the first row of B1 to 1 or 0. Using the dynamic updating of cell formulas once you have it written once it fills in the rest.

So you would start out;

A   B
1   1
1   =IF(A2=1, B1+1, 0)
1   
0   
0   
1   
1   
1   
1   
0   
0   
0   

Then fill in;

A   B
1   1
1   =IF(A2=1, B1+1, 0)
1   =IF(A3=1, B2+1, 0)
0   =IF(A4=1, B3+1, 0)
0   =IF(A5=1, B4+1, 0)
1   =IF(A6=1, B5+1, 0)
1   =IF(A7=1, B6+1, 0)
1   =IF(A8=1, B7+1, 0)
1   =IF(A9=1, B8+1, 0)
0   =IF(A10=1, B9+1, 0)
0   =IF(A11=1, B10+1, 0)
0   =IF(A12=1, B11+1, 0)

And here the result in column B is;

A   B
1   1
1   2
1   3
0   0
0   0
1   1
1   2
1   3
1   4
0   0
0   0
0   0

Hopefully the logic is extendable to Google Docs.

Answer 2

In Excel you can use a single formula to get the maximum number of consecutive 1s, i.e.

=MAX(FREQUENCY(IF(A2:A100=1,ROW(A2:A100)),IF(A2:A100<>1,ROW(A2:A100))))

confirmed with CTRL+SHIFT+ENTER

In googledocs you can use the same formula but wrap in arrayformula rather than use CSE, i.e.

=arrayformula(MAX(FREQUENCY(IF(A2:A100=1,ROW(A2:A100)),IF(A2:A100<>1,ROW(A2:A100)))))

Assumes data in A2:A100 without blanks