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/tutorials...rogress-i.aspx

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

--
Regards!
Stefi



€žRenan Germano€� ezt Ã*rta:

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/tutorials...rogress-i.aspx
.

Adjust ranges!

--
Regards!
Stefi



€žRenan Germano€� ezt Ã*rta:

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/tutorials...rogress-i.aspx
.

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

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/tutorials...rogress-i.aspx
.

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!
Stefi



???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/tutorials...-the-file.aspx

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:B 7),ROW(B2:B7)),IF(B2:B7<LOOKUP("zzz",B2:B7),ROW(B 2: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.

--
Biff
Microsoft Excel MVP


<Renan Germano wrote in message ...
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/tutorials...rogress-i.aspx

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:B 30<VLOOKUP(99999,$A$2:$G$30,COLUMN()))*(B2:B30<" "))),):B30)&" "&VLOOKUP(99999,$A$2:$G$30,COLUMN())
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Renan Germano" wrote:

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!
Stefi



???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/tutorials...-the-file.aspx
.