Games Behind Formula

I have tried to figure out the formula to calculate how many games behind a team is in a division. I tried the following but comes close to the right calculation but no cigar.


TEam 1 20Wins 5 Losses ----
Team 2 18 wins 7 Losses 2 GB Calculation comes up as 3.

=(B5-B6)+(C6-C5)/2

try:

=((b5-b6)+(c6-c5))/2

Wins Losses Pct Games Behind
Team 1 20(cell B5) 5(cell C5 0800 ---------
Team 2 18(CellB6) 7(Cell C6 0.720 2
Team 3 18(CellB7) 8(cell c7) 0.692 2.5


Subtract 20-18=2 and 7-5=2 2+2=4 /2 equals 2 Games Behind

Subtract 20-18=2 and 8 -5=3, 3=2=5 /2 = 2.5 games behind

Originally Posted by Milomet

Wins Losses Pct Games Behind
Team 1 20(cell B5) 5(cell C5 0800 ---------
Team 2 18(CellB6) 7(Cell C6 0.720 2
Team 3 18(CellB7) 8(cell c7) 0.692 2.5


Subtract 20-18=2 and 7-5=2 2+2=4 /2 equals 2 Games Behind

Subtract 20-18=2 and 8 -5=3, 3=2=5 /2 = 2.5 games behind

A more formula based solution would be:

=((max($b$5:$b$100)-b6)+(c6-(min($c$5:$c$100))/2

This will subract the team with the most wins from the current team, the subtract the losses of the current team from the team with the fewest losses, then add those two number together and finally dividing by 2.

Thanks bSweetOus. It worked

That formula works fine as long as there are no tie games involved. How do I modify the formula to account for ties?

Originally Posted by Bau

That formula works fine as long as there are no tie games involved. How do I modify the formula to account for ties?

Tie games are commonly considered half a win and half a loss. Assuming you are considering them the same, the following should work.

=(((max($B$2:$B$100)+(D2/2))-B2)+((D2/2)+C2-min($C$2:$C$100)))/2

This is assuming "wins" are in column B, "losses" in column C and "ties" in column D. The ranges for max and min would start at the uppermost cell that contains a win or loss and end with the bottom most cell that contains a value involving the items you're comparing.

For example, if you have different divisions of four teams each and the wins and losses for the first division are located in cells B2:C5, your max and min ranges would be $B$2:$B$5 and $C$2:$C$5, respectively.

Also note that teams that have completed a different number of games can skew the games behind computation and winning percentage is a better indicator of placement.

Hope this helps!