How to Find the current "Streak"

[iamthesgt]

So this is for a sports-tracking worksheet. There is a column of wins, losses, and ties, denoted by either "W", "L", or "T". I want to find the current streak the team is on, outputted as say "W6" for a 6-game winning streak.

[TABLE="width: 200"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]C5[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]C6[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]C7[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]C8[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]C9[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]C10[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Answer[/TD]
[TD]W3[/TD]
[/TR]
</tbody>[/TABLE]

(Example data)
Now there may be empty cells at the bottom of the column (games that have yet to be played). There are spaces for 20 games (cells C5 to C24). I know how to find the most recent game played. I do this by:

=[COLOR=#000000][FONT=arial]OFFSET([/FONT][/COLOR][COLOR=#109618][FONT=arial]C4[/FONT][/COLOR][COLOR=#000000][FONT=arial],MATCH(2,INDEX(1/([/FONT][/COLOR][COLOR=#DD5511][FONT=arial]C5:C24[/FONT][/COLOR][COLOR=#000000][FONT=arial]<>""),0)),0)[/FONT][/COLOR]

Which returns the result of the last game played (in the example, "W").

Then to find out if the cells above are the same result, I can compare them, but I don't know how to keep comparing them. This is what I have:

[COLOR=#000000][FONT=arial]=OFFSET([/FONT][/COLOR][COLOR=#109618][FONT=arial]C4[/FONT][/COLOR][COLOR=#000000][FONT=arial],MATCH(2,INDEX(1/([/FONT][/COLOR][COLOR=#DD5511][FONT=arial]C5:C24[/FONT][/COLOR][COLOR=#000000][FONT=arial]<>""),0)),0)&1+COUNTIF(INDIRECT("C"&4+MATCH(2,INDEX(1/([/FONT][/COLOR][COLOR=#DD5511][FONT=arial]C5:C24[/FONT][/COLOR][COLOR=#000000][FONT=arial]<>""),0))-1),"="&INDIRECT("C"&4+MATCH(2,INDEX(1/([/FONT][/COLOR][COLOR=#DD5511][FONT=arial]C5:C24[/FONT][/COLOR][COLOR=#000000][FONT=arial]<>""),0))))[/FONT][/COLOR]

This compares the cell above the most recent result, and adds 1 to the count if it matches. The problem is to keep doing this, I'd have to nest a bunch of statements to get it to work (in the example, it would only return "W2"). Can anyone help?

 

[Aladin Akyurek]

It's almost never helpful describing a problem and the desired result in terms of a formula. ?What would be the result if we had:

C8 = L

C10 = L

 

[iamthesgt]

It's almost never helpful describing a problem and the desired result in terms of a formula. ?What would be the result if we had:

C8 = L

C10 = L

There will never be a case where there is a blank cell in between two results. If it was C8=L, C9=W, C10=L, then the result should be "L1" (and this is covered).

Basically I want

If it's L,W,L,L,W,W,W -> result should be "W3"
If it's W,L,L,L,W,L,L -> result should be "L2"
If it's W,W,W,W,W,W -> result should be "W6"

 

[Aladin Akyurek]

There will never be a case where there is a blank cell in between two results. If it was C8=L, C9=W, C10=L, then the result should be "L1" (and this is covered).

Basically I want

If it's L,W,L,L,W,W,W -> result should be "W3"
If it's W,L,L,L,W,L,L -> result should be "L2"
If it's W,W,W,W,W,W -> result should be "W6"

These are really excellent specs...

Control+shift+enter, not just enter:

=LOOKUP(REPT("z",255),$C$5:$C$10)&
  LOOKUP(9.99999999999999E+307,FREQUENCY(IF(C5:C10=LOOKUP(REPT("z",255),
  $C$5:$C$10),ROW($C$5:$C$10)),
  IF($C$5:$C$10<>LOOKUP(REPT("z",255),$C$5:$C$10),ROW($C$5:$C$10))))

If you use an auxiliary cell, say D5...

D5, just enter:

=LOOKUP(REPT("z",255),$C$5:$C$10)

D6, control+shift+enter:

=D5&LOOKUP(9.99999999999999E+307,FREQUENCY(IF(C5:C10=D5,ROW($C$5:$C$10)),
  IF($C$5:$C$10<>D5,ROW($C$5:$C$10))))

 

[iamthesgt]

Hmmm...It says "Error: Wrong data type" (I'm testing this on LibreOffice Calc because I don't have Excel. This is actually going into a Google docs sheet, which does not have the Lookup() function, although most other functions work the same.

 

[Aladin Akyurek]

Hmmm...It says "Error: Wrong data type" (I'm testing this on LibreOffice Calc because I don't have Excel. This is actually going into a Google docs sheet, which does not have the Lookup() function, although most other functions work the same.

Replace


=LOOKUP(REPT("z",255),$C$5:$C$10)

with:

=VLOOKUP(REPT("z",255),$C$5:$C$10,1,1)</pre>


and see whether you can get it work on google docs.

 

[iamthesgt]

It tells me "Error. Did not find value zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz"

 

[Aladin Akyurek]

It tells me "Error. Did not find value zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz"

Just tested... Google spreadsheet does not behave like Excel, Gnumeric, etc., regarding:

LOOKUP(9.99999999999999E+307,Reference)

and its equivalent:

VLOOKUP(9.99999999999999E+307,Reference,1,1)

The same problem with:

LOOKUP(REPT("z",255),Reference)

and its equivalent:

VLOOKUP(REPT("z",255),Reference,1,1)

Also observed that testing:

FREQUENCY(IF(Reference="W",ROW(Reference)),IF(Reference<>"W",ROW(Reference))

does not return an array, rather a results range.

It looks like there is no direct mapping possible for the Excel solution.