WIN-DRAW-LOSS? Win Streak Formula

[Fraserj]

	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

 

[JRidge]

In Module:



In Worksheet:

 

[Fraserj]

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.

Macro is now working, I didn't delete the y off Integer... my fault for not seeing it sorry....

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

Obviously this would be I4 (which you didn't do a formula for the cell)...now through the rearrangement of the sheet following your plan.

At the minute for I'm using:

=CONCATENATE(G14,G15,G16,G17,G18)

which are my last 5 games upto now but I have to update each time a new fixture happens, as this spreadsheet isn't for me and the person using it is even more of a beginner than me... is there any way that this can pick up the last 5 games, as the =CONCATENATE function does?

Thank you for the other formulas and macros... really appreciate your time and effort

 

[JRidge]

Obviously this would be I4 (which you didn't do a formula for the cell)...now through the rearrangement of the sheet following your plan.

At the minute for I'm using:
which are my last 5 games upto now but I have to update each time a new fixture happens, as this spreadsheet isn't for me and the person using it is even more of a beginner than me... is there any way that this can pick up the last 5 games, as the =CONCATENATE function does?

Thank you for the other formulas and macros... really appreciate your time and effort

Like i said above, add this to the Worksheet_Change VB Page:

Sub Worksheet_Change(ByVal Target As Excel.Range)    Dim ws7 As Worksheet
Set ws7 = Worksheets("Tabelle1")
With ws7
    With .Cells(Rows.Count, 7).End(xlUp)
        With .Resize(5, 1).Offset(-4, 0)
            ws7.Cells(4, 9) = _
                Join(Application.Transpose(.Cells.Value2), Chr(44))
        End With
    End With
End With
End Sub

Each time the worksheet changes it updates to the last 5 games

 

[Fraserj]

Hello, Sorry JRidge I thought you forgot about me, I used the formula =INDEX(H:H,MATCH(LARGE(A:A,5),A:A,0))&INDEX(H:H,MATCH(LARGE(A:A,4),A:A,0))&INDEX(H:H,MATCH(LARGE(A:A,3),A:A,0))&INDEX(H:H,MATCH(LARGE(A:A,2),A:A,0))&INDEX(H:H,MATCH(LARGE(A:A,1),A:A,0))

Thank you though

 

[JRidge]

No Probs, Glad you got it working with a Formula.

JRidge.