Creating A Formula For "games Back" In A Baseball

[ojduex]

Hello All,

My spread sheet reads column A teamname, B games played, C wins, D Losses, E Ties, F winning % and G GAMES BACK

I'm looking for a formula to determine games back based on wins, losses, ties and also based on the number of games played.

example, team 1 played 9 games and is 8-0-1. Team 2 has played 9 games and is 8-1-0. Team 2 is would be a 1/2 game back. Team 3 has played 8 games and is 8-0-0 also a 1/2 game behind team 1 but tied with team 2.

So in column G it would read team1 "-", Team 2 ".5" and team 3 ".5"

Any and all help would be greatly appreciated!

Thank you!

 

[Seti]

This has been cross posted here.

Given the responses at OzGrid, you will need to supply more examples. For instance, what would a team that is 8-3-0 be? 2-5-3?

 

[ojduex]

ok, team 1 8-0-1, zero games back
team 2 8-0-1, zero games back
Team 3 8-1-0 .5 games back
Team 4 6-2-1, 2 games back
Team 5 5-3-1, 2.5 games back
Team 6 5-4-0, 3.5 games back
Team 7 4-5-0, 4.5 games back

 

[Seti]

This may work, although it does not produce the same answer as a team that is 5-3-1. I get 3 rather than 2.5.

=((2*$C$2+$E$2)-(2*C3+E3))/2

where the team in first is in row 2, wins in column C, losses in column D and ties in column E.

 

[ojduex]

I think this actually works. Thank you very much!

 

[baodinh]

Try "=ROUND(0.5*((C$2-C2)+(D2-D$2))+(1/SUM(C2:E2)*(E2-E$2)),1)" in cell G2 and copy down.