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

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.
 

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

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
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:

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

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?
 

Some videos you may like

This Week's Hot Topics

Top