# Count streak of alternate 1 and 0

#### 15minoffame

##### New Member
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!

 C 1 0 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

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

#### Crystalyzer

##### Board Regular
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.

• 15minoffame

#### Peter_SSs

##### MrExcel MVP, Moderator
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
CD
10
20
30
41
50
60
71
80
90
100
111
120
130
140
150
161
170
181
190
201
210
 Count Streak

Last edited:
• 15minoffame

#### 15minoffame

##### New Member
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.
HTML:
<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
 1 299.7 2 298.28 1 3 298.21 2 4 295.87 3 5 297.62 0 6 297 1 7 295.4 2 8 296.77 0 9 293.24 1 10 288.06 2 11 290.42 0 12 294.35 0 13 293.08 1 14 288.53 2 15 291.27 0 16 293.24 0 17 296.28 0 18 295.95 1 19 298.88 0 20 298.4 1 21 299.28 0 22 297.97 1 23 300.03 0 24 298.99 1

<tbody>
</tbody>

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).>

Last edited:

#### 15minoffame

##### New Member
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

##### Well-known Member
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

#### 15minoffame

##### New Member
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

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

#### Peter_SSs

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

#### 15minoffame

##### New Member
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?