Count streak of alternate 1 and 0

15minoffame

Board Regular
Joined
Nov 26, 2014
Messages
55
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
10
20
30
41
50
60
71
80
90
100
111
120
130
140
150
161
170
181
190
201
210

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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
106
20
30
41
50
60
71
80
90
100
111
120
130
140
150
161
170
181
190
201
210
Count Streak
 
Last edited:
Upvote 0
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
1299.7
2298.281
3298.212
4295.873
5297.620
62971
7295.42
8296.770
9293.241
10288.062
11290.420
12294.350
13293.081
14288.532
15291.270
16293.240
17296.280
18295.951
19298.880
20298.41
21299.280
22297.971
23300.030
24298.991

<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:
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Using the HTML code &lt ; (without the space) but you'll probably forget that way unless you post regularly.
 
Upvote 0
So, for the sample data in post 4, what result(s) do you want and can you explain in words why?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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