WIN-DRAW-LOSS? Win Streak Formula

Fraserj

Board Regular
Joined
Nov 29, 2015
Messages
63
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
If the colour formatting isn't possible, it doesn't matter, I just wanted it to learn how to do it really
 
Upvote 0
The first part of your question can be done quite easily:
PHP:
if(B2=D2,'D',if(A2="NMFC",if(B2<D2,"L","W"),if(B2<D2,"W","L")))
 
Last edited by a moderator:
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top