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

 

[JRidge]

 

[Fraserj]

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...

I've put the dates of the results in Column A... I've clicked on the Module and it's telling me "Function countlast() As Integery = 0" is wrong, coming up red...Is there something I need to do, sorry i'm a beginner basically when it comes to Macros, I was hoping to do it without so it would work on a Mobile device, but if it's the only way i'll use this.

Just so you know what i'm doing and what steps i'm doing wrong... I right click on tab...View Code... "Clicked on MODULE1"... Pasted that first code into it.... clicked on the relevant worksheet VBA Projects... Pasted 2nd macro you have created.... Then I have put =countlast() in as a formula into G2.

Is that right, and if I was to drag that to another worksheet, would it work, or would I have to type something into a macro for that to be done?

Sorry and Thank You

 

[JRidge]

"Function countlast() As Integery = 0" is wrong, coming up red...

Function countlast() As Integery 
y = 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

 

[Fraserj]

Still not working, I don't know what I'm doing wrong D:

 

[JRidge]

Still not working, I don't know what I'm doing wrong D:

Change Worksheet_Change to this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    
    If Target.Address = Range("A:G").Address Then
    Call countlast
    End If
End Sub

 

[Fraserj]

[/IMG]

[/IMG]
~

[/IMG

Still not working

 

[JRidge]

Strange works fine for me??

 

[Fraserj]

Strange works fine for me??

****.... Is there a way of doing it with a formula rather than Macro?

 

[Fraserj]

"D" "A" "M" "N" that was meant to be haha

 

[JRidge]

Ok Finished product:



In G2:



In I2: This is currently not automatic, you must select I2 and hit enter so it refreashes.



In I3:




Hope this works for you.