WIN-DRAW-LOSS? Win Streak Formula

[Fraserj]

[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Home Team
[/TD]
[TD]Home Goals
[/TD]
[TD]-
[/TD]
[TD]Away Goals
[/TD]
[TD]Away Team
[/TD]
[TD]W-D-L<strike></strike>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NMFC
[/TD]
[TD]2
[/TD]
[TD]-
[/TD]
[TD]1
[/TD]
[TD]Chelsea
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]NMFC
[/TD]
[TD]3
[/TD]
[TD]-
[/TD]
[TD]1
[/TD]
[TD]Norwich
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Liverpool
[/TD]
[TD]3
[/TD]
[TD]-
[/TD]
[TD]2
[/TD]
[TD]NMFC
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]NMFC
[/TD]
[TD]1
[/TD]
[TD]-
[/TD]
[TD]0
[/TD]
[TD]Arsenal
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Manchester United
[/TD]
[TD]2
[/TD]
[TD]-
[/TD]
[TD]1
[/TD]
[TD]NMFC
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Fraserj]

If the colour formatting isn't possible, it doesn't matter, I just wanted it to learn how to do it really

 

[Fraserj]

Bump

 

[Noodleski]

The first part of your question can be done quite easily:

if(B2=D2,'D',if(A2="NMFC",if(B2<D2,"L","W"),if(B2<D2,"W","L")))

 

[RoryA]

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]

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]

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

=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")))[>

 

[Fraserj]

Appreciate this guys, trying to understand how to do them... I'm sure i'll get there

 

[Fraserj]

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]

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

In a Normal Module (Macro) add:

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:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Call countlast
End Sub

In G2 :

=countlast()

In my worksheet Column A is Dates...