Counting a streak

  • Thread starter Thread starter @Homeonthecouch
  • Start date Start date
@

@Homeonthecouch

Is it possible to count and report the greater streak in a set of streaks?
e.g.
A1 1
A2 1
A3 1
A4 1
A5 1
A6 0
A7 1
A8 1
A9 0


The cell would report the streak as being 5 as it is greater than the A7:A8
streak of 2

As always any help is appreciated

Andrew.
 
This isn't very elegant....but
try this:

With
A2:A20 containing your list of values
(NOTE: the list cannot start in Row_1)

Then...this ARRAY FORMULA returns the longest streak of consecutive values
in A2:A20

B1:
=MAX(SMALL(IF((A2:A21=A1:A20)*(A2:A21<>A3:A22),ROW(A2:A21),10^99),ROW($A$1:INDEX(A:A,SUMPRODUCT((A2:A21=A1:A20)*(A2:A21<>A3:A22)))))-SMALL(IF((A2:A21<>A1:A20)*(A2:A21=A3:A22),ROW(A2:A21),10^99),ROW($A$1:INDEX(A:A,SUMPRODUCT((A2:A21<>A1:A20)*(A2:A21=A3:A22))-1)))+1)

Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: That formula will also count consecutive blanks in its computations.

Note_3: Since text wrap will definitely impact the display, there are NO
spaces in that formula.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Try this:

Entered as an arry using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(LARGE(IF(rng<>1,ROW(rng),0),ROW(INDIRECT("1:"&ROWS(rng)-1)))-LARGE(IF(rng<>1,ROW(rng),0),ROW(INDIRECT("2:"&ROWS(rng)))))-1

Biff
 
Try this array formula (ctrl+shift+enter to execute):

=MAX(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10=0,ROW(A1:A10))))

note: the range should include one more row than the data in case the
last row is 1
 
Very nice, Biff....I went with the SMALL function...which wasn't anywhere
near as efficient as your use of LARGE.

(When I first used your formula, the count was off, but then I realized that
the refernced range must either start at Row_1 or compensate for the gap.)

***********
Best Regards,
Ron

XL2002, WinXP
 
=MAX(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10=0,ROW(A1:A10))))
note: the range should include one more row than the data in case the
last row is 1
Click to expand...

Seems to work ok for me without having to do that.

I know you based this on the sample data:

IF(A1:A10=0

Changing to:

IF(A1:A10<>1

Gives it much more flexibility.

Biff
 
Hi, Lori....
Regarding: =MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21=0,ROW(A1:A21))))

I've seen you post formulas like that before and had the opinion that, since
the FREQUENCY function is a bit tough for people to wrap their minds around,
it might be too difficult to grasp....
But, I just took another look at the alternatives (especially mine!)...and
had cause to reconsider.
FREQUENCY is easy! (relatively speaking)

For what it's worth....I'm going with FREQUENCY in the future (maybe with a
brief primer on how it works).

I've been playing with ways to avoid CSE with it, since everybody I work
with knows how to break array formulas, but never remembers how to fix
them.....So far, here's what I came up with:
=MAX(FREQUENCY(INDEX(ROW(A1:A21)/((A1:A21=1)+(A1:A21<>1)/10^99),0),INDEX(ROW(A1:A21)/((A1:A21<>1)+(A1:A21=1)/10^99),0)))

This array version may be better, though (at least for my crew):
=MAX(FREQUENCY(IF(A1:A21=1,ROW(A1:A21)),IF(A1:A21=0,ROW(A1:A21))))+N("ctrl+shift+enter")
Since the reminder is ignored by the formula

Anyway, that's the round-about way of saying "Nice work". : )
***********
Regards,
Ron

XL2002, WinXP
 
Just come back to have a look and see if anyone had any ideas for my problem
and well..... what can I say?

I'm off to try all the suggestions and as always am amazed at the responses.

Many many thanks to all who have given this some thought.

Andrew
 
Thanks Ron, but I can't claim full credit- Bernard Liengme has had
similar examples before.
Biff- good feedback...you don't need the extra row not even when the
range only contains 1's which seems strange.
 
I like this formula. As Ron said, a lot of people really don't understand
how Frequency works. This formula is very easy to illustrate if you break it
down into its individual components. And when you see those components
illustrated the logic is very easy to understand.

Biff
 
I found this one worked best for me too.

Many Many thanks.

I so wish I had gone to the school of newsgroups as a kid. I'm learning so
much more :)

:D

Andrew
 
Back
Top