Count "1" in a serial in a row

Remal

New Member
Joined
Feb 21, 2018
Messages
2
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é :eek:
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
you could do it like this:


Excel 2010
ABCD
1START
2114largest
3192nd Largest
41
51
61
71
81
91
100
110
120
130
141
151
161
171
181
191
201
211
221
230
240
250
261
271
281
291
300
311
321
331
341
351
361
371
381
391
401
411
421
431
441
Sheet4
Cell Formulas
RangeFormula
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
 
Upvote 0
Thanks that's working
But why do you use cell A3:A45 when the range of cells is A2:A44?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top