Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    identify streaks (Excel 2003 (SP3))

    I have a column of data consisting of ones and zeros, representing say "heads" or "tails".
    I'm looking for a way to automatically identify "streaks", such as 5 consecutive ones, or 7 consecutive zeros.
    And/or have a result at the bottom of the column that lists the longest "heads" streak and the longest "tails" streak in the column.

    Any help would be appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Your intermediate formula only seem to work if the last item is a 1 and the next to last is a 0. Other combos lead to #NA errors...

    If you put in in B2 the formula:
    =IF(A2=A1,1+B1,1)
    And copy it down the column it works fine (as well as eliminating the array formulas which can make the calcs sluggish).

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Thanks, that's not only correct but also much more efficient!

    I'll edit my previous reply.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Attachment replaced by HansV with a new one using Steve (sdckapr)'s suggestion - the original one was incorrect. Thanks, Steve!

    Somebody will probably come up with a more elegant solution, but the attached workbook shows one way of doing it using intermediate formulas.
    The formulas in B23 and B24 are array formulas i.e. confirmed with Ctrl+Shift+Enter.
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Here is a slight enhancement to Hans' file. It uses conditional formatting to highlight the streak yellow for 0s and green for 1s. If there is more than 1 "max streak" for the number, the first is highlighted

    Steve
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Much obliged. It does just what I need.
    I must ask, though, what are the "last row", "first row" things in columns C & D ?
    I just used the formulae in column B, as I didn't understand the purpose of C & D.

    Thanks again!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: identify streaks (Excel 2003 (SP3))

    They are used to set the conditional formats

    The last row for each is the row for the end of the longest streak (it is the minimum row when the 1 or the 0 has the max number of the streak). The first row is the start of the longest streak (the lastrow - the length of the streak +1)

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Gotcha!

    I'll have to implement that.

    Thanks again.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Here is another option that does not use the first and last rows, and marks all the "longest streaks" not just the first one...

    Steve
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: identify streaks (Excel 2003 (SP3))

    I believe I implemented the conditional formatting correctly.
    Something funny happens in the last row.
    Hit the F9 key a few times to "toss the coin" and sooner or later the last row or rows will be incorrectly highlighted.
    Attached Files Attached Files

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: identify streaks (Excel 2003 (SP3))

    Move the 2 MAX values to something different than column F. On occasion the cell that is highlighted incorrectly is coincidently the max number of rows above the MAX so the conditional formatting thinks that max is part of the streak ...

    Steve

Posting Permissions

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