Counting Wins And Losses

D

Daviboy30

New Member
Joined
Nov 7, 2021
Messages
33
Office Version
  1. 2019
Platform
  1. MacOS
I'm running a scoresheet for my league and I need to track each player's wins and losses record throughout the season, including the previous season, so that I can assign a proper player ranking. If a player wins or loses 7 out of 10 matches his ranking goes up or down, depending on how he (or she) does. Players often do not play every week, but I still need to track their wins and losses through multiple seasons. More often than not player ranking don't change because they don't meet the 7 out of ten threshold, but when they do, I want the spreadsheet to automatically post the new ranking.

In the attached Mini-sheet example Joe's ranking remained the same because over a period of 13 weeks he played 10 matches, but did not meet the 7 out of 10 wins or losses threshold. Meanwhile Jack's ranking went from 6 up to 7 because over a period of 15 weeks he played 10 matches and won 7 of them. In Mary's example, her ranking went down because over a period of 14 weeks she played 10 matches and lost 7 times. If a player rank goes up then the counting of wins and losses starts fresh.

The problem I'm having is how to automatically track a player's last 10 matches whether he plays the matches in 10 weeks, 10 months or even 10 years.

IFTestBook2.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
3PlayerCurrent RankCurrent WinsCurrent LossesNew Rank1/11/81/151/221/292/52/122/192/263/53/123/193/264/24/94/164/234/30
4Joe4654WWWLLWWLWWWLWL
5Jack6737WWLWWLWWLWW
6Mary3372LWLWLLLWWLLL
Sheet1
Cell Formulas
RangeFormula
C4C4=COUNTIF(K4:W4,"W")
D4:D5D4=COUNTIF(H4:W4,"L")
E4:E6E4=IF(C4>6, B4+1, IF(D4>6, B4-1, IF(B4=2, 2, IF(B4=3, 3, IF(B4=4, 4, IF(B4=5, 5, IF(B4=6, 6, IF(B4=7, 7))))))))
C5C5=COUNTIF(I5:W5,"W")
C6C6=COUNTIF(J6:W6,"W")
D6D6=COUNTIF(J6:W6,"L")
 

Attachments

  • Screen Shot 2021-11-07 at 4.52.18 PM.png
    Screen Shot 2021-11-07 at 4.52.18 PM.png
    99.1 KB · Views: 35
Sort by date Sort by votes
  1. I think the calculations required are too complex for standard formulas. (Happy to be proved wrong though)
  2. Therefore I am proposing a vba suggestion.
  3. A problem I see is how (or if) the New Rank will eventually get moved to the Current Rank column. My suggested code below shows the New Rank in that column but after 'OK' is pressed for the message box that value is moved to the Current Rank column.
  4. I am proposing inserting a new column (F) to record where the last reset occurred. This will be used the next time a cell in that row is entered. The column could be hidden but I'd suggest leave it visible while testing.
Test this in a copy of your workbook.
This is a worksheet event code and will run automatically whenever a single cell is changed beyond row 3 and beyond the Last Reset week for that player. Any change on or before that Last Reset week will not effect a re-calculation. To implement the code ..
First insert the new column F & its heading then
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a As Variant
  Dim W As Long, L As Long, K As Long, i As Long, j As Long, r As Long, wk As Long
  Dim OldRank As Long, NewRank As Long, UpDown As Long, LastReset As Long
  
  If Target.CountLarge = 1 And Target.Column > 6 And Target.Row > 3 Then
    r = Target.Row
    LastReset = Range("F" & r).Value
    wk = Target.Column - 6
    If wk > LastReset Then
    OldRank = Range("B" & r).Value
      a = Split(" " & Join(Application.Index(Range(Cells(r, LastReset + 7), Cells(r, Columns.Count).End(xlToLeft)).Value, 1, 0)))
      For i = 1 To UBound(a)
        Do
          If Len(a(i + j)) > 0 Then
            If UCase(a(i + j)) = "W" Then
              W = W + 1
            Else
              L = L + 1
            End If
            K = K + 1
            If K = 10 Then
              If W > 6 Then
                UpDown = UpDown + 1
                LastReset = i + j
                i = i + j
              ElseIf L > 6 Then
                UpDown = UpDown - 1
                LastReset = i + j
                i = i + j
              End If
              Exit Do
            End If
          End If
          j = j + 1
        Loop Until i + j = UBound(a) + 1 Or K = 10
        If i + j = UBound(a) + 1 Then
          i = UBound(a)
        Else
          W = 0
          L = 0
          K = 0
          j = 0
        End If
      Next i
      NewRank = Cells(r, "B").Value + UpDown
      If NewRank < 2 Then NewRank = 2
      If NewRank > 7 Then NewRank = 7
      Application.EnableEvents = False
      Cells(r, "C").Resize(, 4).Value = Array(W, L, NewRank, LastReset)
      MsgBox "New Rank for " & Range("A" & r) & " will now be moved to Current Rank and New Rank cell cleared"
      Range("B" & r).Value = NewRank
      Range("E" & r).ClearContents
      Application.EnableEvents = True
    End If
  End If
End Sub

So, for my test I started with this (but dates in row 3 would be fine)

Daviboy30_1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
3PlayerCurrent RankCurrent WinsCurrent LossesNew RankLast Reset12345678910111213141516171819202122232425262728293031323334
4Joe4WWWWWLWWWWWWLWLWLLLWWWWWLWWLWLLW
5Jon6WWWWWWWLLLLLLLWWWWWWWLLLLLLLWWWLWW
6JenWWWWL
WL


I then re-entered the "W" in G4, G5 and G6 (re-entering any result cell would do) one at a time and this is the result:

Daviboy30_1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
3PlayerCurrent RankCurrent WinsCurrent LossesNew RankLast Reset12345678910111213141516171819202122232425262728293031323334
4Joe62329WWWWWLWWWWWWLWLWLLLWWWWWLWWLWLLW
5Jon72131WWWWWWWLLLLLLLWWWWWWWLLLLLLLWWWLWW
6Jen2410WWWWL
WL
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I followed your instructions and copied a pasted the code into my workbook. I cleared all the data and started fresh. It appeared to work fine until it reached the 11th data entry (Q4). Attached are screen-captures of the messages I received and the Minisheet:

IFTestBook3-Peter.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1
2
3PlayerCurrent RankCurrent WinsCurrent LossesNew RankLast Reset12345678910111213141516171819202122232425262728293031323334
4Joe61010WWWWWLWLWWW
5Jon500
6
Sheet1
Cell Formulas
RangeFormula
C5C5=LEN(RIGHT(CONCAT(F5:ZZ5),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(F5:ZZ5),10),"W",""))
D5D5=LEN(RIGHT(CONCAT(F5:ZZ5),10))-C5
 

Attachments

  • Screen Shot 2021-11-09 at 3.55.24 PM.png
    Screen Shot 2021-11-09 at 3.55.24 PM.png
    254.5 KB · Views: 7
Upvote 0
I have not been able to reproduce that error. If you can reproduce the error, click 'Debug' and advise which line in the code is highlighted.

Getting the error may also result in your 'events' becoming disabled, meaning that further worksheet entries would not be processed. I will address more fully later but for now, if you think that happens, just close right out of Excel and then open it up again. That should reset the event trapping.

Also, in further testing, I found that message box for every entry rather annoying so I added the blue code below. That should mean that you only see the new rank and get the message if the rank is to change.

Rich (BB code): 
If NewRank <> OldRank Then MsgBox "New Rank for " & Range("A" & r) & " will now be moved to Current Rank and New Rank cell cleared"
 
Upvote 0
Daviboy30 said:
Where do you put the If NewRank code?
Click to expand...
You add the blue code in front of the existing (black) MsgBox line, exactly as shown in post 13.

That is unrelated to the error you are getting though. You still need to address my comments about that (unless you are no longer getting an error)
 
Upvote 0
Hi Daviboy30,

I see you are already working a VBA solution from @Peter_SSs but I wanted to complete my function solution, if only for my own satisfaction.

I've changed the New Rank formula to include the cap you requested.
I've inserted the new column for Date of Last Rank Change so if you manually change the Current Rank field then that's where you'd enter the date of the change.
I've made the New Rank to conditionally format as red if an actual change is required (Note: Sarah and Jim are capped so no actual rank change is needed).

Daviboy30.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2
3PlayerCurrent RankCurrent WinsCurrent LossesNew RankDate of Last Rank Change1-Jan8-Jan15-Jan22-Jan29-Jan5-Feb12-Feb19-Feb26-Feb5-Mar12-Mar19-Mar26-Mar2-Apr9-Apr16-Apr23-Apr30-Apr7-May14-May21-May28-May
4Joe4644WWWLLWWLWWWLWL
5Jack6737WWLWWLWWLWW
6Mary3372LWLWLLLWWLLL
7Sarah7100705-Mar-21WWWWWWWWWWWWWWWWWWWW
8Jim2372LLLLLLLWWW
9Kyle5005WLWWWWWWL
3rd
Cell Formulas
RangeFormula
H3:AB3H3=G3+7
C4:C9C4=IFERROR(COUNTIF(OFFSET($F4,,AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10)),"W"),0)
D4:D9D4=IFERROR(COUNTIF(OFFSET($F4,,AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10)),"L"),0)
E4:E9E4=IF(C4>6,MIN(B4+1,7),IF(D4>6,MAX(B4-1,2),B4))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9Expression=$E9<>$B9textNO
E4:E8,E10:E17Expression=$E4<>$B4textNO
 
Upvote 0
Peter_SSs said:
You add the blue code in front of the existing (black) MsgBox line, exactly as shown in post 13.

That is unrelated to the error you are getting though. You still need to address my comments about that (unless you are no longer getting an error)
Click to expand...
So, I think I've added the new code in the proper place and I am no longer getting the annoying message.
I haven't had time to fully test the new code yet because I wanted to put really data in. In so doing, I added another column after column A, so now the first two columns are Last Name and First Name. But when I started inputting real data, the second column automatically filled in the Current Rank number. See the uploaded image. I assume the VBA code is telling it to put in the rank in the second column. How do you tell it to put the info in the correct columns? I don't know much code. I used to do some HTML, but that was a long time ago.
 

Attachments

  • Screen Shot 2021-11-09 at 9.29.46 PM.png
    Screen Shot 2021-11-09 at 9.29.46 PM.png
    37.5 KB · Views: 8
Upvote 0
Toadstool said:
Hi Daviboy30,

I see you are already working a VBA solution from @Peter_SSs but I wanted to complete my function solution, if only for my own satisfaction.

I've changed the New Rank formula to include the cap you requested.
I've inserted the new column for Date of Last Rank Change so if you manually change the Current Rank field then that's where you'd enter the date of the change.
I've made the New Rank to conditionally format as red if an actual change is required (Note: Sarah and Jim are capped so no actual rank change is needed).

Daviboy30.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2
3PlayerCurrent RankCurrent WinsCurrent LossesNew RankDate of Last Rank Change1-Jan8-Jan15-Jan22-Jan29-Jan5-Feb12-Feb19-Feb26-Feb5-Mar12-Mar19-Mar26-Mar2-Apr9-Apr16-Apr23-Apr30-Apr7-May14-May21-May28-May
4Joe4644WWWLLWWLWWWLWL
5Jack6737WWLWWLWWLWW
6Mary3372LWLWLLLWWLLL
7Sarah7100705-Mar-21WWWWWWWWWWWWWWWWWWWW
8Jim2372LLLLLLLWWW
9Kyle5005WLWWWWWWL
3rd
Cell Formulas
RangeFormula
H3:AB3H3=G3+7
C4:C9C4=IFERROR(COUNTIF(OFFSET($F4,,AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10)),"W"),0)
D4:D9D4=IFERROR(COUNTIF(OFFSET($F4,,AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($G$4:$XAA$4)-COLUMN($F$4)/(($G4:$XAA4<>"")*(COLUMN($G4:$XAA4)>=MATCH(MAX(F4+7,$G$3+1),$G$3:$XAA$3,1)+COLUMN($F$4))),10)),"L"),0)
E4:E9E4=IF(C4>6,MIN(B4+1,7),IF(D4>6,MAX(B4-1,2),B4))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9Expression=$E9<>$B9textNO
E4:E8,E10:E17Expression=$E4<>$B4textNO
Click to expand...
I appreciate your efforts. I'd still like to be able solve this problem using only Excel's built-in formulas.
 
Upvote 0
Daviboy30 said:
I added another column after column A,
Click to expand...
Until we determine whether we can establish a code that does what you want, I would suggest sticking to the format/columns of the original data. vba does not automatically adjust to things like column addition like formulas do so more work will be needed for a changed format. However, no point in putting time into that unless we know the process is going to work. So, do any tests of my code in a dummy file with the original layout for now.

I still have not heard anything more about your error.
 
Upvote 0
I removed the extra column after column A, I cleared all the data, then restarted Excel. I then entered a"W" in the first column under "Jon" (G4) and got the error message. Attached is a screen capture of the VBA code.

IFTestBook4-Peter.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZFAFBFCFDFEFFFGFHFIFJFKFLFMFNFOFPFQFRFSFTFUFV
12021
2JulyAugustSeptemberOctoberNovemberDecember
3PlayerCurrent RankCurrent WinsCurrent LossesNew RankLast ResetTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFr
4First Name13141516171819202122232425262728293031123456789101112131415161718192021222324252627282930311234567891011121314151617181920212223242526272829301234567891011121314151617181920212223242526272829303112345678910111213141516171819202122232425262728293012345678910111213141516171819202122232425262728293031
5Joe610W
6Jon200
7Rich207LLLLLLL
8
Sheet1
Cell Formulas
RangeFormula
C5:C6C5=LEN(RIGHT(CONCAT(F5:AVY5),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(F5:AVY5),10),"W",""))
D5,D7D5=LEN(RIGHT(CONCAT(F5:AVY5),10))-C5
D6D6=LEN(RIGHT(CONCAT(G6:AVZ6),10))-C6
C7C7=LEN(RIGHT(CONCAT(F7:AVY8),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(F7:AVY8),10),"W",""))
 

Attachments

  • Screen Shot 2021-11-10 at 8.12.02 AM.png
    Screen Shot 2021-11-10 at 8.12.02 AM.png
    169.7 KB · Views: 7
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Rank winning percentage for sport game
Replies
9
Views
2K
Peter_SSs
Peter_SSs
K
  • Question Question
Calculating player win-loss records
Replies
1
Views
1K
KWD
K
JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
117
JenniferMurphy
JenniferMurphy
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
423
Fluff
Fluff
GMC The Macro Man
  • Solved
Using =SUM(SMALL to count but omitting Zero
2
Replies
11
Views
245
GMC The Macro Man
GMC The Macro Man

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

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