Count streak of alternate 1 and 0

[15minoffame]

Hi,
I have ones & zeros in column C. Is there a formula that will show me a streak of alternate ones & zeros like at the bottom where there's six in a row?

Thank you!

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"]C[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Crystalyzer]

Put this formula in Cell D2 and copy it down the column

=IF(C2<>C1,D1+1,0)

It will accumulate all the alternate 1 & 0 combinations until there is a duplicate and then enter zero and start over.

 

[Peter_SSs]

See if this is what you want. It should give you the longest streak of alternate values.
It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Or should the result be 7 because C15:C21 are alternating?

Excel Workbook
	C	D
1	0	6
2	0
3	0
4	1
5	0
6	0
7	1
8	0
9	0
10	0
11	1
12	0
13	0
14	0
15	0
16	1
17	0
18	1
19	0
20	1
21	0
Count Streak

 

[15minoffame]

Hi Crystalyzer & Peter,
I realized after I sent it, my formula has more than ones and zeros. I'm trying to find out how often a stock alternate between an up and a down day. Column B is the price. Column C formula is: =IF(B2<b1,c2+1,0). column="" d="" should="" calculate="" the="" alternate="" streak.

<b1,c2+1,0). i="" need="" column="" d="" to="" calculate="" the="" alternate="" streak.=""
<b1,c2+1,0). i="" need="" column="" d="" to="" calculate="" the="" alternate="" days="" streak.="" hopefully="" this="" clarifies="" it="" much="" better.

Thanks again!

B C D[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]299.7[/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]298.28[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]298.21[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]295.87[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]297.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]297[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]295.4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]296.77[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]293.24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]288.06[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]290.42[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]294.35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]293.08[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]288.53[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]291.27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]293.24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]296.28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]295.95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]298.88[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]298.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]299.28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]297.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]300.03[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]298.99[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Put this formula in Cell D2 and copy it down the column

=IF(C2<>C1,D1+1,0)

It will accumulate all the alternate 1 & 0 combinations until there is a duplicate and then enter zero and start over.

</b1,c2+1,0).></b1,c2+1,0).></b1,c2+1,0).>

 

[15minoffame]

I'm not sure why my previous post was cut off. Column C formula: =IF(B2<B1,C2+1,0). Column D needs to calculate the alternate streak.

Thank you

 

[MARK858]

I'm not sure why my previous post was cut off. Column C formula: =IF(B2<B1,C2+1,0). Column D needs to calculate the alternate streak.

Thank you

Just to help it is because the board interprets the < sign followed by a letter as HTML code, the easiest way around it is to put a space after the < sign but anyway what you tried to post was

=IF(B2<b1,c2+1,0)

 

[15minoffame]

Now it makes sense! How were you able to post it when there isn't a space after the < sign?

Thanks Mark!

Just to help it is because the board interprets the < sign followed by a letter as HTML code, the easiest way around it is to put a space after the < sign but anyway what you tried to post was

 

[MARK858]

Using the HTML code &lt ; (without the space) but you'll probably forget that way unless you post regularly.

 

[Peter_SSs]

So, for the sample data in post 4, what result(s) do you want and can you explain in words why?

 

[15minoffame]

I'm looking for a formula to count a streak of alternate one and zero in Column D. This would tell me how many days in a row a stock has alternated between an up and a down day.

So, for the sample data in post 4, what result(s) do you want and can you explain in words why?