# Calculating the current win streak

#### mjennaro

##### New Member
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>

Last edited:

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### barry houdini

##### MrExcel MVP
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

##### MrExcel MVP
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.

##### MrExcel MVP
Similar to a query posted earlier...

Define BigNum as referring to:

=9.99999999999999E+307

J2, control+shift+enter, not just enter:
Rich (BB code):
``````=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...
Rich (BB code):
``=LOOKUP(BigNum,A2:I2)``

J2 becomes:
Rich (BB code):
``````=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

##### MrExcel MVP

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

Thanks Marcelo #### mjennaro

##### New Member
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

##### MrExcel MVP

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

#### mjennaro

##### New Member

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

##### MrExcel MVP
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

##### New Member
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

Replies
3
Views
114
Replies
23
Views
2K
Replies
4
Views
52
Replies
3
Views
366
Replies
2
Views
367