Win Loss Streak Formula

[crobbins05]

I need a formula that will track a current w/l streak. The current formula I am using is:
=LOOKUP(1E+100,FREQUENCY(IF(L4:L7=LOOKUP("zzz",L4:L7),ROW(L4:L7)),IF(L4:L7<>LOOKUP("zzz",L4:L7),ROW(L4:L7))))&"
"&LOOKUP("zzz",L4:L7)

This formula will return w,w,w,l,l,w,w as 2W. The problem I have is that if there is a blank cell in the range it interrupts the streak.
For example w,w,w,l,l, ,l will return 1L instead of 3L.
Also w,w,w,l,l, , will return 0L. Can anyone help?

 

[T. Valko]

I need a formula that will track a current w/l streak. The current formula I am using is:
=LOOKUP(1E+100,FREQUENCY(IF(L4:L7=LOOKUP("zzz",L4:L7),ROW(L4:L7)),IF(L4:L7<>LOOKUP("zzz",L4:L7),ROW(L4:L7))))&"
"&LOOKUP("zzz",L4:L7)

This formula will return w,w,w,l,l,w,w as 2W. The problem I have is that if there is a blank cell in the range it interrupts the streak.
For example w,w,w,l,l, ,l will return 1L instead of 3L.
Also w,w,w,l,l, , will return 0L. Can anyone help?

You only have a 3 cell range, L4:L7, so how can you have all of this in that range: w,w,w,l,l,w,w as ?



Try this...

=LOOKUP(1E100,FREQUENCY(IF(L4:L15=LOOKUP("zzz",L4:L15),ROW(L4:L15)),IF(L4:L15<>LOOKUP("zzz",L4:L15),IF(L4:L15<>"",ROW(L4:L15)))))&" "&LOOKUP("zzz",L4:L15)

 

[crobbins05]

Thanks for the quick reply! I have expanded the range and that is where I learned about the blanks issue. Currently I have the range small but it will be a growing range as wins and losses compile. It is the newest "game" that causes me problems. I want to enter the new "game" and have the formula range automatically expand and ignore the data in the w/l column until it is entered.

 

[T. Valko]

Thanks for the quick reply! I have expanded the range and that is where I learned about the blanks issue. Currently I have the range small but it will be a growing range as wins and losses compile. It is the newest "game" that causes me problems. I want to enter the new "game" and have the formula range automatically expand and ignore the data in the w/l column until it is entered.

Well, the formula I suggested will account for those empty cells.

What version of Excel are you using?

 

[crobbins05]

Well, the formula I suggested will account for those empty cells.

What version of Excel are you using?

Excel 2010. I just tried your formula again and it worked this time! I must have been missing a parentheses or something. Thanks for your help!!

 

[T. Valko]

Excel 2010. I just tried your formula again and it worked this time! I must have been missing a parentheses or something. Thanks for your help!!

Good deal. Thanks for the feedback!