# WIN-DRAW-LOSS? Win Streak Formula

#### Fraserj

##### Board Regular
 A B C D E F 1 Home Team Home Goals - Away Goals Away Team W-D-L 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

### 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
If the colour formatting isn't possible, it doesn't matter, I just wanted it to learn how to do it really

Bump

#### Noodleski

##### Active Member
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

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

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

<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
Appreciate this guys, trying to understand how to do them... I'm sure i'll get there

#### Fraserj

##### Board Regular
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
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``````

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

Replies
23
Views
2K
Replies
3
Views
152
Replies
3
Views
366
Replies
2
Views
163
Replies
4
Views
58