Calculating the current win streak

[mjennaro]

So, there are a lot of questions related to displaying a cell with the current winning or losing streak for a team.

There are also a lot of variations in the way people have their data setup and this affects the formula needed to retrieve what is needed.

My data set has the streak displayed with a 1 for a win and a 0 for a loss and a .5 for a tie. The data is all contained in 3 rows as follows:

a	b	c	d	e	f	g	h	i	j
1	0	1	.5	1	1	1	1		W4
0	0	0	0	1	1	0	.5		T1
1	1	1	1	0	1	0	0		L2

<tbody>
</tbody>

What I am looking for is a way to calculate the formula in column J so that the streak is displayed. If my data increased by 1 more column as follows, the output in column j would be automatically updated to show:

a	b	c	d	e	f	g	h	i	j
1	0	1	.5	1	1	1	1	0	L1
0	0	0	0	1	1	0	.5	1	W1
1	1	1	1	0	1	0	0	1	W1

<tbody>
</tbody>

Thanks in advance.

 

[barry houdini]

Hello mjennaro, welcome to MrExcel, try this formula for J2

=HLOOKUP(LOOKUP(2,A2:I2),{0,1,0.5;"L","W","T"},2,0)&COUNTIF(INDEX(A2:I2,IFERROR(MATCH(2,INDEX(1/(A2:I2<>LOOKUP(2,A2:I2))/(A2:I2<>""),0)),0)+1):I2,LOOKUP(2,A2:I2)

)

 

[Marcelo Branco]

Hello mjennaro, welcome to MrExcel, try this formula for J2


=HLOOKUP(LOOKUP(2,A2:I2),{0,1,0.5;"L","W","T"},2,0)&COUNTIF(INDEX(A2:I2,IFERROR(MATCH(2,INDEX(1/(A2:I2<>LOOKUP(2,A2:I2))/(A2:I2<>""),0)),0)+1):I2,LOOKUP(2,A2:I2)

)

Hi Barry,

I think there is a typo in your formula - the parenthesis in red is misplaced

Shouldn't it be ?

=HLOOKUP(LOOKUP(2,A2:I2),{0,1,0.5;"L","W","T"},2,0)&COUNTIF(INDEX(A2:I2,IFERROR(MATCH(2,INDEX(1/(A2:I2<>LOOKUP(2,A2:I2))/(A2:I2<>""),0)),0)+1):I2,LOOKUP(2,A2:I2))


Another possible solution

J2
=LOOKUP(LOOKUP(2,$A2:$I2),{0;0.5;1},{"L";"T";"W"})&COUNT($A2:$I2)-IFERROR(LOOKUP(2,1/(($A2:$I2<>"")*($A2:$I2<>LOOKUP(2,$A2:$I2))),COLUMN($A2:$I2)-COLUMN($A2)+1),0)

M.

 

[Aladin Akyurek]

Similar to a query posted earlier...

Define BigNum as referring to:

=9.99999999999999E+307

J2, control+shift+enter, not just enter:

=LOOKUP(LOOKUP(BigNum,A2:I2),{0,"L";0.5,"T";1,"W"})&
  LOOKUP(BigNum,1/(1/FREQUENCY(IF(IF(A2:I2="","#",A2:I2)=LOOKUP(BigNum,A2:I2),COLUMN(A2:I2)),
  IF(A2:I2<>LOOKUP(BigNum,A2:I2),COLUMN(A2:I2)))))

This set up won't be affected by the empty cells in-between.

If you allow for the following in K2, copied down...

=LOOKUP(BigNum,A2:I2)

J2 becomes:

=LOOKUP(K2,{0,"L";0.5,"T";1,"W"})&
  LOOKUP(BigNum,1/(1/FREQUENCY(IF(IF(A2:I2="","#",A2:I2)=K2,COLUMN(A2:I2)),
  IF(A2:I2<>K2,COLUMN(A2:I2)))))

 

[barry houdini]

I think there is a typo in your formula - the parenthesis in red is misplaced

Thanks Marcelo

 

[mjennaro]

Thank you, Barry and Marcelo. I was not able to replicate Marcelo's solution across the various rows in my workbook. However, Barry's did work across all rows (missing end parenthasis, not withstanding). One further question: I forgot to mention that I have the word phrase "bye" in some cells to indicate that the team does not play a game that week. Any insight on how to ignore this word phrase yet continue to calculate numbers that make up the streak?

a	b	c	d	e	f	g	h	i	j
1	0	1	0.5	1	1	bye	1	1	W4
0	0	0	0	1	1	0	0.5	bye	T1
1	1	1	1	0	1	bye	0	0	L2

<tbody>
</tbody>

Thanks.

Michael

 

[Aladin Akyurek]

Thank you, Barry and Marcelo. I was not able to replicate Marcelo's solution across the various rows in my workbook. However, Barry's did work across all rows (missing end parenthasis, not withstanding). One further question: I forgot to mention that I have the word phrase "bye" in some cells to indicate that the team does not play a game that week. Any insight on how to ignore this word phrase yet continue to calculate numbers that make up the streak?

a	b	c	d	e	f	g	h	i	j
1		1	0.5	1	1	bye	1	1	W4
				1	1		0.5	bye	T1
1	1	1	1		1	bye			L2

<TBODY>
</TBODY>

Thanks.

Michael

What about the third option?!...

 

[mjennaro]

What about the third option?!...

Ha! Aladin, I am a man of simple means that sticks with an option once it is proven to meet his needs. If you are saying that your solution solves the "bye" problem as well, I will try it and let you know as much.

Thanks.

Michael

 

[barry houdini]

A small change to my suggestion can make it work with "bye"s too

=HLOOKUP(LOOKUP(2,A2:I2), {0,1,0.5;"L","W","T"},2,0)&COUNTIF(INDEX(A2:I2, IFERROR(MATCH(2,INDEX(1/(A2:I2<>LOOKUP(2,A2:I2))/(A2:I2<>"")/(A2:I2<>"bye"),0)),0)+1):I2,LOOKUP(2,A2:I2))

 

[mjennaro]

Mr. Houdini, you are both a scholar and a gentleman. Your title as Mr. Excel MVP is most warranted. This formula works like a charm. Thanks so very much.

Michael