Counting current streak

[Renan Germano]

Hello! I have a table with the results of each team?s recent games. In column A are the game dates, and in each other column is a team name (B1,C1,D1..). In B2,C2,D2 I put the result for that game, either W, D or L.
How can i make a formula that gives me the current streak for each team? It should give me, for example, 2 wins, 2 losses or 3 draws..
Tks


Submitted via EggHeadCafe - Software Developer Portal of Choice
Scriptless ASP Progress Indicator
http://www.eggheadcafe.com/tutorial...5-f5cebaba13a8/scriptless-asp-progress-i.aspx

 

[Stefi]

=COUNTIF(B2:B4,"w")&" wins, "&COUNTIF(B2:B4,"l")&" losses,
"&COUNTIF(B2:B4,"d")&" draws"

--
Regards!
Stefi



„Renan Germano†ezt írta:

 

[Stefi]

Adjust ranges!

--
Regards!
Stefi



„Renan Germano†ezt írta:

 

[Clarity]

Hi,

this can be acheived by using th COUNTIF formula:

=COUNTIF(Range,criteria)

You will need three formula for each column (one for each type):

=COUNTIF(column for team,"W") no of wins
=COUNTIF(column for team,"L") in cell below no of losses
=COUNTIF(column for team,"D") in cell below no of draws

You can use the concatenate formula to pull the result together with text if
you need to. ie =CONCATENATE(COUNTIF(B1:B10,"W"), " wins")

Hope this helps.

 

[Renan Germano]

Let?s say one teams current form is: wwwddll
With this formula, the result is: 3 wins 2 draws 2 losses

But what I want it to say it?s just the latest streak.. in this example it would be 2 losses.
And if this team wins the next game, it sould change the result to 1 win..

Regards,
RG



Stefi wrote:

=COUNTIF(B2:B4,"w")&" wins, "&COUNTIF(B2:B4,"l")&"
15-Jan-10

=COUNTIF(B2:B4,"w")&" wins, "&COUNTIF(B2:B4,"l")&" losses
"&COUNTIF(B2:B4,"d")&" draws

-
Regards
Stef


???Renan Germano??? ezt ??rta:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# : Get and set the file and Directory attributes
http://www.eggheadcafe.com/tutorial...b21-e06f68ae2a41/c--get-and-set-the-file.aspx

 

[T. Valko]

Try this...

B2 = W
B3 = W
B4 = D
B5 = L
B6 = L
B7 = L

Array entered** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Result: 3 L

Note: this will not work if there are empty cells within the range.

 

[Luke M]

A bit lengthy, but I believe this will give you the latest streak. Assuming
you put the formula into row 31, and your table goes to column G:

=COUNTA(OFFSET(B1,SUMPRODUCT(MAX(ROW(B2:B30)*(B2:B30<>VLOOKUP(99999,$A$2:$G$30,COLUMN()))*(B2:B30<>""))),):B30)&" "&VLOOKUP(99999,$A$2:$G$30,COLUMN())