Counting Wins And Losses

[Daviboy30]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	Player	Current Rank	Current Wins	Current Losses	New Rank	1/1	1/8	1/15	1/22	1/29	2/5	2/12	2/19	2/26	3/5	3/12	3/19	3/26	4/2	4/9	4/16	4/23	4/30
4	Joe	4	6	5	4	W	W	W	L		L	W	W	L		W	W	W	L		W		L
5	Jack	6	7	3	7	W			W	L		W	W	L			W	W		L		W	W
6	Mary	3	3	7	2		L	W		L	W	L	L	L	W	W			L	L			L
Sheet1

Cell Formulas
Range	Formula
C4	C4	=COUNTIF(K4:W4,"W")
D4:D5	D4	=COUNTIF(H4:W4,"L")
E4:E6	E4	=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))))))))
C5	C5	=COUNTIF(I5:W5,"W")
C6	C6	=COUNTIF(J6:W6,"W")
D6	D6	=COUNTIF(J6:W6,"L")

 

[Toadstool]

Hi Daviboy30,

The last 10 entries for Joe start 2/5 in column K so I think he has 4 losses and not 5.

I've not looked at your New Rank formula as I've assumed you just want to populate the Current Wins and Current Losses columns based on the last 10 results.

You don't say how many data columns there may be, just it could be 10 years of results, so I've taken it up to column XAA.

The AGGREGATE looks for the last 10 columns with results and returns that to the OFFSET which sets the range of the COUNTIF.

Daviboy30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3	Player	Current Rank	Current Wins	Current Losses	New Rank	01-Jan-21	08-Jan-21	15-Jan-21	22-Jan-21	29-Jan-21	05-Feb-21	12-Feb-21	19-Feb-21	26-Feb-21	05-Mar-21	12-Mar-21	19-Mar-21	26-Mar-21	02-Apr-21	09-Apr-21	16-Apr-21	23-Apr-21	30-Apr-21
4	Joe	4	6	4	4	W	W	W	L		L	W	W	L		W	W	W	L		W		L
5	Jack	6	7	3	7	W			W	L		W	W	L			W	W		L		W	W
6	Mary	3	3	7	2		L	W		L	W	L	L	L	W	W			L	L			L
Sheet1

Cell Formulas
Range	Formula
C4:C6	C4	=COUNTIF(OFFSET($E4,,AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10)),"W")
D4:D6	D4	=COUNTIF(OFFSET($E4,,AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10)),"L")
E4:E6	E4	=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))))))))

 

[Peter_SSs]

Here is another option for looking at counts for the last 10 results. These also work if less than 10 results exist.

21 11 08.xlsm
	A		C	D		F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3	Player		Current Wins	Current Losses		1/1	1/8	1/15	1/22	1/29	2/5	2/12	2/19	2/26	3/5	3/12	3/19	3/26	4/2	4/9	4/16	4/23	4/30
4	Joe		6	4		W	W	W	L		L	W	W	L		W	W	W	L		W		L
5	Jack		7	3		W			W	L		W	W	L			W	W		L		W	W
6	Mary		3	7			L	W		L	W	L	L	L	W	W			L	L			L
7	Ann		1	2			L				W	L
WL

Cell Formulas
Range	Formula
C4:C7	C4	=LEN(RIGHT(CONCAT(F4:ZZ4),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(F4:ZZ4),10),"W",""))
D4:D7	D4	=LEN(RIGHT(CONCAT(F4:ZZ4),10))-C4

 

[Daviboy30]

Hi Daviboy30,

The last 10 entries for Joe start 2/5 in column K so I think he has 4 losses and not 5.

I've not looked at your New Rank formula as I've assumed you just want to populate the Current Wins and Current Losses columns based on the last 10 results.

You don't say how many data columns there may be, just it could be 10 years of results, so I've taken it up to column XAA.

The AGGREGATE looks for the last 10 columns with results and returns that to the OFFSET which sets the range of the COUNTIF.

Daviboy30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3	Player	Current Rank	Current Wins	Current Losses	New Rank	01-Jan-21	08-Jan-21	15-Jan-21	22-Jan-21	29-Jan-21	05-Feb-21	12-Feb-21	19-Feb-21	26-Feb-21	05-Mar-21	12-Mar-21	19-Mar-21	26-Mar-21	02-Apr-21	09-Apr-21	16-Apr-21	23-Apr-21	30-Apr-21
4	Joe	4	6	4	4	W	W	W	L		L	W	W	L		W	W	W	L		W		L
5	Jack	6	7	3	7	W			W	L		W	W	L			W	W		L		W	W
6	Mary	3	3	7	2		L	W		L	W	L	L	L	W	W			L	L			L
Sheet1

Cell Formulas
Range	Formula
C4:C6	C4	=COUNTIF(OFFSET($E4,,AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10)),"W")
D4:D6	D4	=COUNTIF(OFFSET($E4,,AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10),,COLUMN($XAA$1)-AGGREGATE(14,6,COLUMN($F$4:$XFD$4)-COLUMN($E$4)/($F4:$XFD4<>""),10)),"L")
E4:E6	E4	=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))))))))

Hi Toadstool,
Thank you for the formula. I works great! Except for one thing: If a players rank moves up or down, then the Current Wins and Current Losses need to be reset to zero. Again, thank you!

 

[Daviboy30]

Here is another option for looking at counts for the last 10 results. These also work if less than 10 results exist.

21 11 08.xlsm
	A		C	D		F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3	Player		Current Wins	Current Losses		1/1	1/8	1/15	1/22	1/29	2/5	2/12	2/19	2/26	3/5	3/12	3/19	3/26	4/2	4/9	4/16	4/23	4/30
4	Joe		6	4		W	W	W	L		L	W	W	L		W	W	W	L		W		L
5	Jack		7	3		W			W	L		W	W	L			W	W		L		W	W
6	Mary		3	7			L	W		L	W	L	L	L	W	W			L	L			L
7	Ann		1	2			L				W	L
WL

Cell Formulas
Range	Formula
C4:C7	C4	=LEN(RIGHT(CONCAT(F4:ZZ4),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(F4:ZZ4),10),"W",""))
D4:D7	D4	=LEN(RIGHT(CONCAT(F4:ZZ4),10))-C4

Peter_SSs,
Thank you for the reply. One thing I didn't make clear in my problem description was that if a player's rank moves up or down any particular week, then the Current Wins and Current Losses need to be reset back to zero.

 

[Toadstool]

The New Rank uses Current Wins and Current Losses for its calculation so it will result in a circular reference error.

 

[Peter_SSs]

One thing I didn't make clear in my problem description was that if a player's rank moves up or down any particular week, then the Current Wins and Current Losses need to be reset back to zero.

Yes, I understood that to be the case, I just don't know just how you would do that.
For example, what values would go in C4:E5 for the sample data shown, and how do you get those values if doing it manually (no formulas)?
Can you talk us through that process?
For simplicity I have just numbered the weeks 1 to 34.

Daviboy30.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
3	Player	Current Rank	Current Wins	Current Losses	New Rank	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
4	Joe	4				W	L	W	W	W	L	W	W	W	W	W	W	L	W	L	W	L			L	L	W	W	W	W	W	L	W	W	L	W	L	L	W
5	Jon	5				W	W	W	W	W	W	W	L	L	L	L	L	L	L	W	W	W	W	W	W	W	L	L	L	L	L	L	L	W	W	W	L	W	W
WL

 

[Toadstool]

"If a players rank moves up or down, then the Current Wins and Current Losses need to be reset to zero."
So if a player rank changes, up or down, then they must play at least another 10 matches before another rank change would be considered?

If that's the case, and I note Current Rank is a manual entry, then when you update the Current Rank you could use a new column "Date of Last Rank Change" and enter the date that occurred. Then our formulae could ignore any dates before then in the calculations.

Would that work for you?

 

[Daviboy30]

"If a players rank moves up or down, then the Current Wins and Current Losses need to be reset to zero."
So if a player rank changes, up or down, then they must play at least another 10 matches before another rank change would be considered?

If that's the case, and I note Current Rank is a manual entry, then when you update the Current Rank you could use a new column "Date of Last Rank Change" and enter the date that occurred. Then our formulae could ignore any dates before then in the calculations.

Would that work for you?

Yes, I think that would work.

 

[Daviboy30]

Yes, I understood that to be the case, I just don't know just how you would do that.
For example, what values would go in C4:E5 for the sample data shown, and how do you get those values if doing it manually (no formulas)?
Can you talk us through that process?
For simplicity I have just numbered the weeks 1 to 34.

Daviboy30.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
3	Player	Current Rank	Current Wins	Current Losses	New Rank	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
4	Joe	4				W	L	W	W	W	L	W	W	W	W	W	W	L	W	L	W	L			L	L	W	W	W	W	W	L	W	W	L	W	L	L	W
5	Jon	5				W	W	W	W	W	W	W	L	L	L	L	L	L	L	W	W	W	W	W	W	W	L	L	L	L	L	L	L	W	W	W	L	W	W
WL

According to our league rules a player rank goes up or down if he wins or loses 7 out of 10 matches. If he wins 7 matches before playing all 10 matches, he does not move up or down until all 10 matches are played. In other words, a new player could win 7 matches in a row, but still has to play three more matches before he officially moves up. Also, a player could play 20 matches and never move up or down because in a ten match span he may not reach the 7-out-of-10 threshold.

According to your scenario, Joe would have moved up twice: once after 10 weeks and again after the 29th week. Because after each rank move up or down "resets the clock", by the 34th week Joe will have 2 wins in the Current Wins column and 3 losses in the Current Losses column.

According to your scenerio Jon would have moved up twice, then moved back down once: moved up to a 6 after 10 weeks, again moved up to a 7 after the 21st week, then moved back down to a 6 after the 31st week. After the 34th week Jon will have 2 wins in the Current Wins column and 1 Loss in the Current Losses column.

One thing I didn't mention because I want to keep it simple is that the lowest rank a player can have is a 2 and the highest rank you can have is 7. You can never go higher than a 7 and lower than a 2.

By the way, I've ordered a book on Excel formulas. Maybe I'll learn something. Thanks for your help.