Count "1" in a serial in a row

[Remal]

Hi

A have a row with 0 and 1 and they comes in a serial.

A wants a formula to calculate the largest/ second largest serial of 1's in a row. I wants to variate were to start and stop, sometimes I have to start counting in a middle of a serial of 1's

1111111110000000111111000000000001111111111 = this should gives 10 as the largest and 9 as the second largest.


René

 

[VBA Geek]

you could do it like this:

Excel 2010
	A	B	C	D
1	START
2	1		14	largest
3	1		9	2nd Largest
4	1
5	1
6	1
7	1
8	1
9	1
10	0
11	0
12	0
13	0
14	1
15	1
16	1
17	1
18	1
19	1
20	1
21	1
22	1
23	0
24	0
25	0
26	1
27	1
28	1
29	1
30	0
31	1
32	1
33	1
34	1
35	1
36	1
37	1
38	1
39	1
40	1
41	1
42	1
43	1
44	1
Sheet4

Cell Formulas
Range	Formula
C2	{=LARGE(FREQUENCY(IF(A2:A44<>1,FALSE,IF(A2:A44=A3:A45,ROW(A3:A45))),IF(A2:A44<>1,FALSE,IF(A2:A44<>A3:A45,ROW(A3:A45))))+1,1)}
C3	{=LARGE(FREQUENCY(IF(A3:A45<>1,FALSE,IF(A3:A45=A4:A46,ROW(A4:A46))),IF(A3:A45<>1,FALSE,IF(A3:A45<>A4:A46,ROW(A4:A46))))+1,2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

regarding the range where to start, it depends on how you want to do it. you could create a dynamic range for example

 

[Remal]

Thanks that's working
But why do you use cell A3:A45 when the range of cells is A2:A44?

 

[VBA Geek]

it needs to compare each cell with the previous one