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:

[TABLE="width: 100, align: center"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD].5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]W4[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD].5[/TD]
[TD][/TD]
[TD]T1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]L2[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 100, align: center"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD].5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]L1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD].5[/TD]
[TD]1[/TD]
[TD]W1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]W1[/TD]
[/TR]
</tbody>[/TABLE]


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?

[TABLE="width: 50"]
<tbody>[TR]
[TD="align: center"]a
[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]e[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"]h[/TD]
[TD="align: center"]i[/TD]
[TD="align: center"]j[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0.5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]bye[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]W4[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]bye[/TD]
[TD]T1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]bye[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]L2[/TD]
[/TR]
</tbody>[/TABLE]

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?

[TABLE="width: 50"]
<TBODY>[TR]
[TD="align: center"]a

[/TD]
[TD="align: center"]b
[/TD]
[TD="align: center"]c
[/TD]
[TD="align: center"]d
[/TD]
[TD="align: center"]e
[/TD]
[TD="align: center"]f
[/TD]
[TD="align: center"]g
[/TD]
[TD="align: center"]h
[/TD]
[TD="align: center"]i
[/TD]
[TD="align: center"]j
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]0.5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]bye
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]W4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]0.5
[/TD]
[TD]bye
[/TD]
[TD]T1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]bye
[/TD]
[TD][/TD]
[TD][/TD]
[TD]L2
[/TD]
[/TR]
</TBODY>[/TABLE]

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