Rank winning percentage for sport game

[breilly00]

In our Bocce ball league, 2 games are played in each match. If a team wins both games they get a score of 2 wins and 0 losses. If a team wins one game and loses the other game they get a score of 1 win and 1 loss. If the team loses both games they get a score of 0 wins and 2 losses. The score keeper manually enters the score and number of games played which in this example would be 2

How can I use number of games played to rank a team higher than another team with less games played. This is required because if a team wins by a forfeit then the number of wins would increase but the number of games played would not increase because a forfeit is not considered that a game was played. This forfeit results in the number of games played being less than the total of the Wins and Losses. E.G.

Team 1 - 55 wins and 2 losses = 96%-win percentage

Team 2 - 1 win and 0 losses = 100%-win percentage

Team 1 should rank higher because they have a higher number of wins.

Our current sheet contains Col A for Wins, Col B for Losses and Col C for games played and Col D for team name. There are no ties and helper columns can be added if necessary. For each game if a team wins a 1 is entered in column A, a loss enters a 1 in column B and a 2 is entered for total games played in Col C. This is what needs to be modified so a win by a forfeit is ranked less than win by playing the game.

Or, if there is a better way to create a sheet that shows the ranking of players based on percentage of wins using games played a new sheet can be created as the league has not yet started.

 

[Micron]

Interesting problem. I was thinking of mixing the Geometric mean into the calculation. That's where you multiply the values and take the square root of the resulting value, but what to do with forfeits? So using that approach (only 2 values to multiply - wins*losses) and factor in the forfeits as total games minus losses (total games includes forfeits). Then apply the SQRT function to the result. If there were:

55 wins, 2 losses:
wins*losses+(games count - losses)
110+(57-2) = 110+55 . SQRT(165) returns 12.84523

1 win, zero losses:
1*0+(2-0) = 0+2 . SQRT(2) returns 1.414214

50 wins, 2 losses, 1 forfeit (53 games):
50*2+(53-2) = 100+51 = 151 . SQRT(151) returns = 12.28821

I'm no mathematician (if I was I might think those calculations are nonsense) but I had fun with this and with only 3 examples, it seems like it might be viable. I suppose you could +(games count - wins) instead and in most cases you'd be adding a smaller number to the wins*losses but it probably won't matter what sort of constant you add as long as it's the same across the board.
HTH
EDIT - I wanted to try 55 wins, 2 losses, 1 forfeit to see where it landed but may run out of editing time.
Nope, still good. Here's that scenario:
55*2+(58-2) = 110+56 = 166 SQRT returns 12.8841 Close, but I imagine you want that to rank lower than 55 wins, 2 losses. Sorry. Will have to see if I can tweak something.

 

[Peter_SSs]

For each game if a team wins a 1 is entered in column A, a loss enters a 1 in column B and a 2 is entered for total games played in Col C.

Does that mean that a particular team will appear on several rows down the sheet?
How/where do you want the results displayed?
Could you create a sample sheet with some dummy data and the expected results calculated manually and entered in, post that with XL2BB and add any further explanation in relation to that specific sample data?

It would also help if we knew what Excel version you were using as different versions have different functions available. Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Micron]

Maybe this solves the forfeit ranking I have in my prior post:

SQRT(ABS(wins*losses - (games count- losses))) ABS is absolute, which ignores negative values. SQRT and ABS would be in all formulas but I've left it out in places to try to add clarity.

55 wins, 2 losses, no forfeits:
55*2-(57-2)=110-55
SQRT of 55 is 7.416198487

1 win, 0 losses, no forfeits:
1*0-(2-0)=-2 apply square root function to the absolute value of -2: SQRT(ABS(-2)) = 1.414214

55 wins, 2 losses, 1 forfeit:
55*2-(58-2)= 110-56 =54 SQRT of 54 is 7.348469228
Now the forfeit ranks slightly lower than the same number of games won and lost but actually played. I imagine a formula guru will have a better approach.

 

[breilly00]

sqrt formula is =SQRT(ABS(E2))
wlcount formula is =(B2*C2)+(D2-C2)

couple questions

team 3 and 4 has a 50% win rate using total games/wins
team 1 has a higher percent win rate 9/1 and should be ranked higher than 3 and 4
team 7 has perfect record and should be top ranked team

Not sure if I used excel XLB22 right i copied and pasted my sample sheet and pasted it in their sheet

Team	win	loss	gp	wl-Count	SQRT
Team1	9​	1​	10​	18​	4.242641​
Team2	1​	9​	10​	10​	3.162278​
Team3	5​	5​	10​	30​	5.477226​
Team4	5​	5​	10​	30​	5.477226​
Team5	0​	0​	0​	0​	0​
team6	10​	0​	2​	2​	1.414214​
team7	10​	0​	10​	10​	3.162278​
team8	1​	0​	1​	1​	1​
team9	0​	1​	1​	0​	0​

 

[Micron]

You're adding the results of the last brackets but I was subtracting. Probably no matter as I doubt either produces a valid result. I guess it depends on what is the correct way to rank teams 6,7 and 8. I got closer with this modified formula but team 8 is still a problem. They all have a perfect record even though 8 has only played one game. I looked at weighted averages but it seems you have to know how much weight to give to one or more of the inputs. I guess I'm out of my league here (pun intended).
=SQRT(ABS(C9/(D9+1)*(C9+D9)))/C9+D9

team	wins	losses	rank
1	9	1	1.74535599
2	1	9	10
3	5	5	5.57735027
4	5	5	5.57735027
5	0	0	#DIV/0!
6	10	0	1
7	10	0	1
8	1	0	1
9	0	1	#DIV/0!

 

[Peter_SSs]

Not sure if I used excel XLB22 right

No. Work carefully through the instructions again. You can test it out in the Test Here forum until you have it sorted.
However, what you have done is at least usable, though XL2BB would be better.

Some further questions.

1. Does your sample above include the expected results as I requested in post #3? I am asking because generally "ranks" are reported as "1" for the top ranked team, "2" for the second ranked team etc. and I can't see anything like that in your sample.
   
   
2. What is the SQRT column for? Is it related to the ranking question?
   
   
3. Is there a reason that the formula =SQRT(ABS(E2)) contains the ABS() function. From your column E formula I cannot see that column E could ever be negative and therefore need ABS.
   
   
   Using the sample worksheet below ..
   
   
4. Could it be that
   Team16 won 1 actual game and lost 1 game on forfeit, and
   Team17 won 1 game on forfeit and lost 1 actual game?
   If so, how would you know which is which and which team should be ranked higher and why? .. or should they be ranked equally?
   
   
5. Team18 and Team19 have very different results but, using your formulas, have exactly the same wl-Count and SQRT.
   Which team should be ranked higher and why? .. or should they be ranked equally?

23 11 17.xlsm
	A	B	C	D	E	F
1	Team	win	loss	gp	wl-Count	SQRT
17	Team16	1	1	1	1	1
18	Team17	1	1	1	1	1
19	Team18	9	1	1	9	3
20	Team19	1	9	9	9	3
Rank

Cell Formulas
Range	Formula
E17:E20	E17	=(B17*C17)+(D17-C17)
F17:F20	F17	=SQRT(ABS(E17))

 

[breilly00]

Micron,

Your suggestion of using square root gave me pause to revisit my options. After a while, I came up with the idea of having 2-win columns. One for wins of games played and one for wins as a result of a forfeit. The spread sheet has an option to enter a percentage that will reduce the number of wins according to the percentage. E.G., if there is 1 win due to a forfeit and the forfeit reduction percentage is 50%, the wins are recalculated to .5. If the percentage is 25%, the wins are recalculated to .25, etc.

The Commish of our league has agreed to use this approach.

I want to express my sincere thanks to you. You and the other individuals who take the time to help us neophytes are the backbone of the “Mr. Excel” site.

 

[Micron]

Glad I was able to help in whatever way. Admittedly it turned out to be harder than I thought given that I was never great at math or statistics in general. Looking again at the solution though I wonder why gp (games played?) for team19 is 9 and not 10 (1 win, 9 losses).

 

[Peter_SSs]

I wonder why gp (games played?) for team19 is 9 and not 10 (1 win, 9 losses).

That was my sample data not the OP's. My reading of the original information ..

This is required because if a team wins by a forfeit then the number of wins would increase but the number of games played would not increase because a forfeit is not considered that a game was played.

.. is that Team 19 could get to those numbers by

• winning 1 game by forfeit and actually losing 9 games (so only 9 games actually played), OR
• actually winning 1 game, actually losing 8 games and losing 1 game by forfeit (again only 9 games actually played)

No way of knowing which of the two options above actually happened though.