# Thread: identify streaks (Excel 2003 (SP3))

1. ## 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. ## 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. ## Re: identify streaks (Excel 2003 (SP3))

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

I'll edit my previous reply.

4. ## 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.

5. ## 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

6. ## 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. ## 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. ## Re: identify streaks (Excel 2003 (SP3))

Gotcha!

I'll have to implement that.

Thanks again.

9. ## 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

10. ## 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.

11. ## 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
•