I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil

You might want to add another tie-breaker if points and goal difference are
equal - perhaps alphabetical order?

If you have a calculation which can go negative and you want it never to go
below 0, then you can use this:

=MAX(0,your_calc)

If your_calc is negative this returns 0, otherwise it returns the value of
your_calc.

Hope this helps.

Pete

" wrote in message
...
I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil

Adding one or more to your rank to eliminate negative numbers would be the
easiest way to fix this... if you didn't want a zero to show up as one, you
could create a hidden helper column that adds one to your current rank value
and use it with the rank function.

" wrote:

I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil

On 16 Apr, 02:05, BoniM wrote:
Adding one or more to your rank to eliminate negative numbers would be the
easiest way to fix this... if you didn't want a zero to show up as one, you
could create a hidden helper column that adds one to your current rank value
and use it with the rank function.



" wrote:
I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil- Hide quoted text -

- Show quoted text -

I've tried that already, but of course 0 would become 1, and -0.500
would become 0.5 and still rank below the teams who have played 0
games, and thus the problem remains!

Thanks for replying anyway
Neil

=IF(I5=0,"",RANK(I5,$I$2:$I$5))
If I5 contains your rank score, this will substitute a blank cell for anyone
with a rank score of zero instead of listing their rank. It will still rank
everyone else as if that rank was there, but will put the blanks at the
bottom with a sort. Then you could hide that column and rank that column for
the numbers you want to see:
=RANK(J2,$J$2:$J$5,1)
Hope that helps... and proves I think better in the am... :-)



" wrote:

On 16 Apr, 02:05, BoniM wrote:
Adding one or more to your rank to eliminate negative numbers would be the
easiest way to fix this... if you didn't want a zero to show up as one, you
could create a hidden helper column that adds one to your current rank value
and use it with the rank function.



" wrote:
I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil- Hide quoted text -

- Show quoted text -

I've tried that already, but of course 0 would become 1, and -0.500
would become 0.5 and still rank below the teams who have played 0
games, and thus the problem remains!

Thanks for replying anyway
Neil

On 16 Apr, 12:25, BoniM wrote:
=IF(I5=0,"",RANK(I5,$I$2:$I$5))
If I5 contains your rank score, this will substitute a blank cell for anyone
with a rank score of zero instead of listing their rank. *It will still rank
everyone else as if that rank was there, but will put the blanks at the
bottom with a sort. *Then you could hide that column and rank that column for
the numbers you want to see:
=RANK(J2,$J$2:$J$5,1)
Hope that helps... and proves I think better in the am... :-)



" wrote:
On 16 Apr, 02:05, BoniM wrote:
Adding one or more to your rank to eliminate negative numbers would be the
easiest way to fix this... if you didn't want a zero to show up as one, you
could create a hidden helper column that adds one to your current rank value
and use it with the rank function.

" wrote:
I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil- Hide quoted text -

- Show quoted text -

I've tried that already, but of course 0 would become 1, and -0.500
would become 0.5 and still rank below the teams who have played 0
games, and thus the problem remains!

Thanks for replying anyway
Neil- Hide quoted text -

- Show quoted text -

That was almost it BoniM, but despite giving blank cells, it still
ranked the others lower!

So, I did this to resolve the problem
=IF(CD4=0,-1,(DB4-DC4)/1000)
Thus, if Cell CD4 is 0 (not matches competed) I make the value -1 and
so this pushes them to the bottom of the rankings

Thanks for suggestions that pushed me to the right solution

Neil

Glad you got it figured out... and happy to help in any small way that I
might have.

" wrote:

On 16 Apr, 12:25, BoniM wrote:
=IF(I5=0,"",RANK(I5,$I$2:$I$5))
If I5 contains your rank score, this will substitute a blank cell for anyone
with a rank score of zero instead of listing their rank. It will still rank
everyone else as if that rank was there, but will put the blanks at the
bottom with a sort. Then you could hide that column and rank that column for
the numbers you want to see:
=RANK(J2,$J$2:$J$5,1)
Hope that helps... and proves I think better in the am... :-)



" wrote:
On 16 Apr, 02:05, BoniM wrote:
Adding one or more to your rank to eliminate negative numbers would be the
easiest way to fix this... if you didn't want a zero to show up as one, you
could create a hidden helper column that adds one to your current rank value
and use it with the rank function.

" wrote:
I am creating a Sports League table, where the ranking is acheived
with the following rules
a) Teams are ranked by Total points scored (2 for a win 1 for a draw)
b) If teams are tied on points then Goal difference will apply

Example
Team A and B have 5 points each, but Team B has +2 Goal difference and
Team A has -1, so B will Rank above A in the League.

To try and achieve this, I have a column that subtracts "Goals
against" from "Goals for" and divide this by 1000. This result is
added in another column to Points
So Team A (above) would have a ranking score of 4.999 and B would be
5.002

My problem though, is this:
In early season, if a team has played no games, they will have a rank
score of 0. Another team may have played some games with no wins or
draws and a negative goal difference will have a negative ranking
score.
So 'RANK()' will place the teams on 0 (played no games) above the
negative ranked teams, and I want them to be at the bottom.

Can anyone please suggest a better way to do this please, so I can
have my table show teams who played no matches at the base of the
chart.

Many thanks and I hope I explained this properly!

Neil- Hide quoted text -

- Show quoted text -

I've tried that already, but of course 0 would become 1, and -0.500
would become 0.5 and still rank below the teams who have played 0
games, and thus the problem remains!

Thanks for replying anyway
Neil- Hide quoted text -

- Show quoted text -

That was almost it BoniM, but despite giving blank cells, it still
ranked the others lower!

So, I did this to resolve the problem
=IF(CD4=0,-1,(DB4-DC4)/1000)
Thus, if Cell CD4 is 0 (not matches competed) I make the value -1 and
so this pushes them to the bottom of the rankings

Thanks for suggestions that pushed me to the right solution

Neil