Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Count streak of alternate 1 and 0

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count streak of alternate 1 and 0

    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

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Location
    Pompano Beach, FL
    Posts
    174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count streak of alternate 1 and 0

    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.
    Using Windows 10, Excel 365. Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Count streak of alternate 1 and 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?

    Count Streak

    CD
    106
    20
    30
    41
    50
    60
    71
    80
    90
    100
    111
    120
    130
    140
    150
    161
    170
    181
    190
    201
    210

    Spreadsheet Formulas
    CellFormula
    D1{=MAX(FREQUENCY(IF(C2:C21<>C1:C20,ROW(C2:C21)),IF(C2:C21=C1:C20,ROW(C2:C21))))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Oct 23rd, 2019 at 12:25 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    Nov 2014
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count streak of alternate 1 and 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
    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

    Quote Originally Posted by Crystalyzer View Post
    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.
    Last edited by 15minoffame; Oct 23rd, 2019 at 04:26 AM.

  5. #5
    New Member
    Join Date
    Nov 2014
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count streak of alternate 1 and 0

    I'm not sure why my previous post was cut off. Column C formula: =IF(B2
    Thank you

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,234
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Count streak of alternate 1 and 0

    Quote Originally Posted by 15minoffame View Post
    I'm not sure why my previous post was cut off. Column C formula: =IF(B2
    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)
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count streak of alternate 1 and 0

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

    Thanks Mark!

    Quote Originally Posted by MARK858 View Post
    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

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,234
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Count streak of alternate 1 and 0

    Using the HTML code &lt ; (without the space) but you'll probably forget that way unless you post regularly.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Count streak of alternate 1 and 0

    So, for the sample data in post 4, what result(s) do you want and can you explain in words why?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    New Member
    Join Date
    Nov 2014
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count streak of alternate 1 and 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.

    Quote Originally Posted by Peter_SSs View Post
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •