simple sports model formulas: (1/(N2/(N2+O2))*Q2), N2=map wins, O2=map losses, Q2=difficulty

glaucun

New member
Joined
Mar 2, 2019
Messages
3
I hope this is the right place.
I'm trying to create a simple model to evaluate Overwatch teams. The equation I want to use divides map win by total maps in a given game then multiplies that by a difficulty rating i give the game. It looks like this:
(1/(N2/(N2+O2))*Q2)
N2=map wins
O2=map losses
Q2=difficulty
The issue I'm having is when a team doesn't win a game in the series so the equation ends up trying to divide 0 by the total games. Excel gives an error from this. The fix i came up with adds 1 game win to every series. That equation looks like this:
(1/((N2+1)/(N2+O2))*Q2)
I'm not happy with this though it seems that teams with a zero win series are punished to heavily. I'm not a math expert at all so just kind of winging it. Thanks for reading I hope it made sense.
 

Dr.Peterson

Elite Member
Joined
Nov 12, 2017
Messages
3,595
I may not understand the game and how you intend to score it, but can you explain why you have the "1/" in the formula? That makes the score lower for more wins; and without it, you would not have your difficulty. I would have expected just (N2/(N2+O2))*Q2.
 

glaucun

New member
Joined
Mar 2, 2019
Messages
3
Nope, you're exactly right. I realized my mistake and changed the formula to exactly that. So a formula with three games looks like this:
=(N2+1)/(N2+O2)Q2+(S2+1)/(S2+T2)V2+(X2+1)/(X2+Y2)*AA2
The difficulty rating is 1-10, 10 being the hardest match so a higher rating equals a better team.
With this equation it seems teams with 0 win series aren't crazy skewed like before but I'm still interested in other ideas.
Thanks for the response!
 

Dr.Peterson

Elite Member
Joined
Nov 12, 2017
Messages
3,595
You shouldn't need the "+ 1". Also, rather than summing three of these, you might want to average them (divide that sum by 3) if there are not always going to be 3 games. What you should do will depend on how you will be using these ratings -- what you will be comparing them to, and what they are intended to represent. (For example, might the better team happen to play only easier games, so they can't get the highest rating?)
 

glaucun

New member
Joined
Mar 2, 2019
Messages
3
I took off the "+1" and didn't get an error so that's good. I will be adding games as the season goes on so averaging seems like a good idea. The better team playing easier games not getting the highest rating is exactly what I'm going for. So far I've tried bouncing the ratings off expected wins vs a team. So I take the teams average wins for, multiply it by the opposing teams average wins against, and take the square root of that. With that i want to factor in the other teams rating or maybe the difference between the 2 teams ratings. Not sure how to do that I'm still playing around with it.
Thanks again!
 
Top