Ranking problem.

[neil40]

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

 

[Pete_UK]

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

 

[BoniM]

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.

 

[neil40]

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.











- 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

 

[BoniM]

=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...

 

[neil40]

=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...







- 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

 

[BoniM]

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