Start Position Of Most Consecutive 1s in Column

[mgirvin]

Dear Smartest Excelers In The World,

Someone ask me this question and I am struggling to find an answer:


Data has 0s and 1s in cells A2:A21. I need a formula that will find the start position of the of the most consecutive 1s.

Since the most consecutive 1s is 4 and it starts in position 13, the formula should evaluate to 13. In the below example, the first yellow 1 is in position 13.

Any ideas?


[TABLE="width: 48"]


<colgroup><col style="width: 48pt;" width="64">
<tbody>[TR]

[TD="class: xl65, width: 64, bgcolor: transparent"]Numbers[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl68, bgcolor: yellow, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl68, bgcolor: yellow, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl68, bgcolor: yellow, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl68, bgcolor: yellow, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[/TR]


</tbody>[/TABLE]

 

[Teethless mama]

Excel Workbook
	A	B	C
1	Numbers	Helper column
2	0		13
3	1	2
4	1	1
5	0
6	1	3
7	1	2
8	1	1
9	0
10	0
11	0
12	1	1
13	0
14	1	4
15	1	3
16	1	2
17	1	1
18	0
19	0	3
20	1	2
21	0	1
Sheet1

 

[circledchicken]

Hi Mike,

Another alternative to Teethless mama's solution might be this:Sheet1<table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0"><thead><tr><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><th>1</th><td style="font-weight: bold;;">Numbers</td><td style="text-align: right;;">13</td></tr><tr ><th>2</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>3</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>4</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>5</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>6</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>7</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>8</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>9</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>10</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>11</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>12</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>13</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>14</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>15</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>16</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>17</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>18</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>19</th><td style="text-align: right;;">0</td><td></td></tr><tr ><th>20</th><td style="text-align: right;;">1</td><td></td></tr><tr ><th>21</th><td style="text-align: right;;">0</td><td>Excel 2010<table ><tr><td style="padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;">

Excel Workbook
Cell	Formula
B1	=SMALL(IF(A1:A22<>1,ROW(A1:A22)),MATCH(MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21)))),FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21))),0))-MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21))))-1
Array Formulas

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.<br />This is based on a solution by Domenic here - http://www.ozgrid.com/forum/showthread.php?t=71645&p=369653#post369653 - and also used here - http://www.mrexcel.com/forum/showthread.php?601194-Count-singles-double-tripple

 

[mgirvin]

Dear Teethless mama,

Thank you for your amazing solution!
I must apologize for taking a week to get back, but I “fell off the edge of the world” there for a while with family vacations and trying to catch up at work.

I love your elegant helper column solution with the essential formula element:

MATCH(0,A2:$A$21,0)-MATCH(1,A2:$A$21,0)

Great idea with the shrinking range and MATCH-MATCH!!! Just awesome!

I settled on this as a helper:

=IF(A2<>1,"",IF(COUNTIF($A$2:$A$21,"<>1")=0,1,MATCH(0,A2:$A$21,0)-MATCH(1,A2:$A$21,0)))

And this as the position finder:

=MATCH(MAX(B2:B21),B2:B21,0)

Thank you very much for the beautiful solution!

 

[mgirvin]

Circledchicken,

Same as I said above:
Thank you for your amazing solution!
I must apologize for taking a week to get back, but I “fell off the edge of the world” there are a while with family vacations and trying to catch up at work.

Before I posted the question, I got this far:

=MATCH(MAX(FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21)))),FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21))),0)

Which gives the position of the max count of 1s in the FREQUENCY produced array. But I stopped because I was stumped about how to find the position in the original data set. Also, I thought there had to be a more efficient formula that would not list the FREQUENCY part so many times. But if you and Domenic are fine with using a solution like the one you posted for a single cell solution, then I am fine with it!!!

After I read your post and appropriated the ideas from your formula, I was still set on using the original data range in the formula and I forced this solution:

=IFERROR(SMALL(IF(A2:A21<>1,ROW(A2:A21)),MATCH(MAX(FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21)))),FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21))),0))-MAX(FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21))))-1-(COUNTIF(A2:A21,1)=0),1)

But the elegance of your formula that adds one row more at the beginning of the range in the FREQUENCY array to deal with the situation where all the numbers are 0s, and adding one more row at the end of the range in SMALL to help with the situation where there are all 1s is staggeringly cool:

=SMALL(IF(A1:A22<>1,ROW(A1:A22)),MATCH(MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21)))),FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21))),0))-MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21))))-1

Thanks sooo much for the awesome solution!

 

[circledchicken]

Circledchicken,

Same as I said above:
Thank you for your amazing solution!
I must apologize for taking a week to get back, but I “fell off the edge of the world” there are a while with family vacations and trying to catch up at work.

Before I posted the question, I got this far:

=MATCH(MAX(FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21)))),FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21))),0)

Which gives the position of the max count of 1s in the FREQUENCY produced array. But I stopped because I was stumped about how to find the position in the original data set. Also, I thought there had to be a more efficient formula that would not list the FREQUENCY part so many times. But if you and Domenic are fine with using a solution like the one you posted for a single cell solution, then I am fine with it!!!

After I read your post and appropriated the ideas from your formula, I was still set on using the original data range in the formula and I forced this solution:

=IFERROR(SMALL(IF(A2:A21<>1,ROW(A2:A21)),MATCH(MAX(FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21)))),FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21))),0))-MAX(FREQUENCY(IF(A2:A21=1,ROW(A2:A21)),IF(A2:A21<>1,ROW(A2:A21))))-1-(COUNTIF(A2:A21,1)=0),1)

But the elegance of your formula that adds one row more at the beginning of the range in the FREQUENCY array to deal with the situation where all the numbers are 0s, and adding one more row at the end of the range in SMALL to help with the situation where there are all 1s is staggeringly cool:

=SMALL(IF(A1:A22<>1,ROW(A1:A22)),MATCH(MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21)))),FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21))),0))-MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21<>1,ROW(A1:A21))))-1

Thanks sooo much for the awesome solution!

Your welcome! Your explanations are incredible. After figuring out how to do something there should be a mgirvin manual that allows people to relearn why they did what they did.

 

[mgirvin]

That is a very kind comment, circledchicken!!

But I must also throw the compliment back at you (boomerang): You and Toothless mama rock!!!! Thanks for the help.

I am in the process of making a video for these amazing solutions, but the formulas posted are really doozies and to try and explain in a way that is fun and understandable is taking me a while!!