Calculating the current win streak

mjennaro

New Member
Joined
Nov 26, 2013
Messages
12
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:

abcdefghij
101.51111W4
0000110.5T1
11110100L2

<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:

abcdefghij
101.511110L1
0000110.51W1
111101001W1

<tbody>
</tbody>


Thanks in advance.
 
Last edited:

Some videos you may like

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Aug 23, 2010
Messages
16,393
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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)))))
 

mjennaro

New Member
Joined
Nov 26, 2013
Messages
12
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
bcdefghij
1010.511bye11W4
00001100.5byeT1
111101bye00L2

<tbody>
</tbody>

Thanks.

Michael
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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

New Member
Joined
Nov 26, 2013
Messages
12
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Nov 26, 2013
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,763
Messages
5,524,756
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top