Winning streak.....

[jackf-nc]

I am trying to figure out 'longest winning' streak. Currently page 2 has column that has either y or n in in cell. Y represents win and N represents loss. What I am trying to do is count most consecutive y in column - between the loss (N) Currently column is filled with y or n - just want the max count of wins, if that makes sense.

Thanks,
Jack

[Tony Valko]

Try this...

Data Range

	A	B	C
1	N		3
2	N
3	Y
4	Y
5	N
6	Y
7	Y
8	Y
9	N
10	N
11	------	------	------

This array formula** entered in C1:

=MAX(FREQUENCY(IF(A1:A10="Y",ROW(A1:A10)),IF(A1:A10<>"Y",ROW(A1:A10))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[jackf-nc]

Ok Tony!

You reply helped perfectly! Now how can I bring the the last date of that winning streak over. NOT THE last win but last date of that streak - Currently y or n's are in page 2 col k dates are in col a.

Thanks Man!
Jack

[FlameRetired]

Piggy-backing on Tony Valko's post try this.

These are all array entered. The first two are helper cells.
The MAX winning streak formula in E1 is the same =MAX(FREQUENCY(IF($A$1:$A$20="y",ROW($1:$20)),IF($A$1:$A$20<>"y",ROW($1:$20))))

Then this MATCH formula to locate the MAX in the array. =MATCH(E1,FREQUENCY(IF($A$1:$A$20="y",ROW($1:$20)),IF($A$1:$A$20<>"y",ROW($1:$20))),0)

Then this formula in D1 to locate the last date in the winning streak. =INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20<>"y",ROW($1:$20)),E2)-1)

Row\Col	A	B	C	D	E
1	n	1/2/2016		1/13/2016	3
2	y	1/3/2016			6
3	y	1/4/2016
4	n	1/5/2016
5	y	1/6/2016
6	n	1/7/2016
7	y	1/8/2016
8	n	1/9/2016
9	n	1/10/2016
10	y	1/11/2016
11	y	1/12/2016
12	y	1/13/2016
13	n	1/14/2016
14	y	1/15/2016
15	y	1/16/2016
16	n	1/17/2016
17	n	1/18/2016
18	n	1/19/2016
19	y	1/20/2016
20	n	1/21/2016

[jackf-nc]

Dave,

Your solution worked perfectly! Only issue that I had was with date portion. You were minused 1 from row on fake sheet. I spent last couple of hours - even creating a new sheet to create the error. It appears when I use the row command I have to start from where actually data starts. Here is what I used on the last part of your EXCELLENT solution! =INDEX($A$11:$A$2000,SMALL(IF($K$11:$K$2000<>"y",ROW($K$11:$K$2000)),Y3)-11) Is it because I start data at row 11?

Am I missing something with the row command. Trust me, I love to analyse the formulas that so many have helped out with, I just want to know why I had to substract 11 instead of 1.

Thank you and Tony for your help!
You guys have been awesome! Thanks for sharing your knowledge!
Thanks again!
Jack

[FlameRetired]

Do you have a sample workbook that demos that? My imagination cannot adequately visualize what you describe.

[jackf-nc]

Here ya go... Again, I so appreciate your help. Am just trying to understand what is going on...Hope you can assist...

[FlameRetired]

OK.

I can see where my first post is misleading. Since everything began in row 1 relative addressing issues are not immediately apparent.

I reworked your last upload. First I reduced the range. It makes the arrays easier to copy and paste so you can see what's going on inside the arrays.

The formula for the MAX win streak remains the same ... with a reduced range ...

=MAX(FREQUENCY(IF($K$4:$K$20="y",ROW($K$4:$K$20)),IF($K$4:$K$20<>"y",ROW($K$4:$K$20))))

The location within the FREQUENCY array of that MAX is still this formula

=MATCH(L2,FREQUENCY(IF($K$4:$K$20="y",ROW($K$4:$K$20)),IF($K$4:$K$20<>"y",ROW($K$4:$K$20))),0)

The formula that locates the last date of the MAX run.

=INDEX($A$4:$A$20,SMALL(IF($K$4:$K$20<>"y",ROW($A$4:$A$20)-MIN(ROW($A$4:$A$20))+1),M2)-1)

This is what needed attention in that last formula. ROW($A$4:$A$20)-MIN(ROW($A$4:$A$20))+1


That 'standardizes' the index number assignments assuring that they always start at 1. While it could just as easily been written as ROW($1:$17) the ROW minus MIN-ROW assures the math is correct and saves you having to look back and mentally calculate if the numbers are right.

The arrays produced by the FREQUENCY function, the two halves of the frequency function are copied and pasted in the attached.

Examine the patterns. See if it helps explain why it always requires subtraction of 1.

[Tony Valko]

=INDEX($A$4:$A$20,SMALL(IF($K$4:$K$20<>"y",ROW($A$4:$A$20)-MIN(ROW($A$4:$A$20))+1),M2)-1)

This is what needed attention in that last formula. ROW($A$4:$A$20)-MIN(ROW($A$4:$A$20))+1

If you index the entire column then you don't need to calculate an "offset correction".

=INDEX($A:$A,SMALL(IF($K$4:$K$20<>"y",ROW($K$4:$K$20)),M2)-1)

Still array entered.

[FlameRetired]

@ Tony Valko

Thank you. I missed that detail.

@ jackf-nc

With that try this

=INDEX(A:A,SMALL(IF($K$4:$K$20<>"y",ROW($A$4:$A$20)),N2)-1)

Like Tony says, done this way there is no need for the offset to reset the array to 1.