WIN-DRAW-LOSS? Win Streak Formula

Fraserj

Board Regular
Joined
Nov 29, 2015
Messages
54
A
B
C
D
E
F
1
Home Team
Home Goals
-
Away Goals
Away Team
W-D-L<strike></strike>
2
NMFC
2
-
1
Chelsea
3
NMFC
3
-
1
Norwich
4
Liverpool
3
-
2
NMFC
5
NMFC
1
-
0
Arsenal
6
Manchester United
2
-
1
NMFC

<tbody>
</tbody>

Hello, My table currently looks like the one above, I need a few formulas which hopefully I will learn from aswell as just putting in my spreadsheet.
First of All, I need "Column F" to determine whether NMFC have a Win, Draw or Loss (W-D-L)


Secondly, Possibly in "Cell G2" to calculate the Win Streak of which they are on now (most recent) plus highest number of Wins in a row NMFC have had in their History in "Cell G3"

And Finally In "Cell G4" is it possible to Create a Form sheet, looking up Criteria in the W-D-L Column, to give a result of the last 5 games, so from the table above it would appear as "WWLWL" with W being green, D being Orange and L being Red. (I don't know if that would be easier to make the W-D-L column results appearing in those colours, I could do that Column from conditional formatting if that isn't the easier option, "I" just don't know how to do it when they're all together

Thank You in advance
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fraserj

Board Regular
Joined
Nov 29, 2015
Messages
54
If the colour formatting isn't possible, it doesn't matter, I just wanted it to learn how to do it really
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
The first part of your question can be done quite easily:
Code:
if(B2=D2,'D',if(A2="NMFC",if(B2<D2,"L","W"),if(B2<D2,"W","L")))
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,499
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

FYI, the board software can get confused thinking things that follow a < sign are html tags. To avoid this there are three options:
1. Use PHP tags (which I did to your post above)
2. Put a space before and after any < symbols.
3. Use the advanced editor (click Go Advanced in the regular one) and turn HTML off. (unfortunately, you can't make that a default).
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
Thanks RoryA. I will remember those pointers for future postings.

And to FraserJ: since I'm not working with an English version of Excel the ' and " could differ from what I have in the formula above.
 

JRidge

Board Regular
Joined
Sep 10, 2013
Messages
106

ADVERTISEMENT

First of All, I need "Column F" to determine whether NMFC have a Win, Draw or Loss (W-D-L)

As per Noodleski's answer above.

<e2,"l","w"),if(c2<e2,"w","l")))[ code]
<e2,"l","w"),if(c2<e2,"w","l")))[ code]
<e2,"l","w"),if(c2<e2,"w","l")))v[ code]
<e2,"l","w"),if(c2<e2,"w","l")))[ php]=""

highest number of Wins in a row NMFC have had in their History in "Cell G3"

Put in G3: Ctrl , Shift and Enter

Code:
=MAX(FREQUENCY(IF(G2:G999="W",ROW(G2:G999)),IF(G2:G999<>"W",ROW(G2:G999))))

Possibly in "Cell G2" to calculate the Win Streak of which they are on now (most recent)

Looking at this now


n "Cell G4" is it possible to Create a Form sheet, looking up Criteria in the W-D-L Column, to give a result of the last 5 games, so from the table above it would appear as "WWLWL" with W being green, D being Orange and L being Red. (I don't know if that would be easier to make the W-D-L column results appearing in those colours,

Looking at this now</e2,"l","w"),if(c2<e2,"w","l")))[></e2,"l","w"),if(c2<e2,"w","l")))v[></e2,"l","w"),if(c2<e2,"w","l")))[></e2,"l","w"),if(c2<e2,"w","l")))[>
 
Last edited:

Fraserj

Board Regular
Joined
Nov 29, 2015
Messages
54
Appreciate this guys, trying to understand how to do them... I'm sure i'll get there
 

Fraserj

Board Regular
Joined
Nov 29, 2015
Messages
54
Thank you, I've used the same one for most losses in a row now aswell... IF I was the do Most games without a win... I'm guessing the formula would be instead of "W" something to do with D and L, How would I put that in? The other ones are more important, I didn't know if it was easy though
 

JRidge

Board Regular
Joined
Sep 10, 2013
Messages
106
in "Cell G2" to calculate the Win Streak of which they are on now (most recent)

In a Normal Module (Macro) add:

Code:
Function countlast() As Integery = 0
For x = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1
    If Cells(x, 7) = "W" Then
       y = y + 1
    Else: x = 0
    End If
Next x
countlast = y
End Function


In Worksheet_Change Vba add:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Call countlast
End Sub

In G2 :

Code:
=countlast()


In my worksheet Column A is Dates...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,766
Messages
5,524,772
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top