Rank within Rank
 

Ok...:rolleyes:

I wondered if anyone can help me with what may seem simple to someone
of more expeience... ;)

I have a ranking system, with the formulae for one of them

Code:
--------------------
=RANK(K30,$K$30:$K$53,0)
--------------------


This ranks them on 'Points' (its a football/soccer league table). I
have done a Vlookup on them, and all is working fine, ofcouse until two
teams have the same amount of points. I knows its possible to sort this
out, but looks complicated and I dont want to do that yet because thats
not quite the end product I want. Before I do this, (If I need to do it)
I want to (after ranking them by points) rank them by Goal Difference,
and if thats the same then Goals For, and if thats the same then by
Alphabetical order...

I know this is possible by going Data Sort and doing it that way but
I want it to change automatically including places of the teams when I
enter the scores in.

Help would be really appreciated!

Thanks


--
excelnewbieian
------------------------------------------------------------------------
excelnewbieian's Profile: http://www.excelforum.com/member.php...o&userid=37380
View this thread: http://www.excelforum.com/showthread...hreadid=570687

Rank within Rank
 

Add a column to calculate a weighted score

=K30*1000+J30*100+COUNTIF($A$30:$A$53,"<="&A30)

where K is the points, J the goal difference, and A the name.

Then rank on this new column.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"excelnewbieian"
<excelnewbieian.2cddxq_1155290105.5873@excelforu m-nospam.com wrote in
message news:excelnewbieian.2cddxq_1155290105.5873@excelfo rum-nospam.com...

Ok...:rolleyes:

I wondered if anyone can help me with what may seem simple to someone
of more expeience... ;)

I have a ranking system, with the formulae for one of them

Code:
--------------------
=RANK(K30,$K$30:$K$53,0)
--------------------


This ranks them on 'Points' (its a football/soccer league table). I
have done a Vlookup on them, and all is working fine, ofcouse until two
teams have the same amount of points. I knows its possible to sort this
out, but looks complicated and I dont want to do that yet because thats
not quite the end product I want. Before I do this, (If I need to do it)
I want to (after ranking them by points) rank them by Goal Difference,
and if thats the same then Goals For, and if thats the same then by
Alphabetical order...

I know this is possible by going Data Sort and doing it that way but
I want it to change automatically including places of the teams when I
enter the scores in.

Help would be really appreciated!

Thanks


--
excelnewbieian
------------------------------------------------------------------------
excelnewbieian's Profile:
http://www.excelforum.com/member.php...o&userid=37380
View this thread: http://www.excelforum.com/showthread...hreadid=570687

Rank within Rank
 

Bob Phillips Wrote:
Add a column to calculate a weighted score

=K30*1000+J30*100+COUNTIF($A$30:$A$53,"<="&A30)

where K is the points, J the goal difference, and A the name.

Then rank on this new column.



After about half an hour thinking on it I did It! Thats sooooo clever,
thank you very much!

However what you wrote is not quite right... Instead of < it should
have been to get it in alphabetical order... not the opposite :P.
Dont worry I know you were just testing me!

Thanks again Bob.


--
excelnewbieian
------------------------------------------------------------------------
excelnewbieian's Profile: http://www.excelforum.com/member.php...o&userid=37380
View this thread: http://www.excelforum.com/showthread...hreadid=570687

Rank within Rank
 

"excelnewbieian"
<excelnewbieian.2cdpqr_1155305406.1593@excelforu m-nospam.com wrote in
message news:excelnewbieian.2cdpqr_1155305406.1593@excelfo rum-nospam.com...

Bob Phillips Wrote:
Add a column to calculate a weighted score

=K30*1000+J30*100+COUNTIF($A$30:$A$53,"<="&A30)

where K is the points, J the goal difference, and A the name.

Then rank on this new column.



After about half an hour thinking on it I did It! Thats sooooo clever,
thank you very much!

However what you wrote is not quite right... Instead of < it should
have been to get it in alphabetical order... not the opposite :P.
Dont worry I know you were just testing me!

Do you know I started with that, then changed it ...?

Like excelnewbieian I have been seeking a solution to my league problem and from what I can see you have resolved matters. However I am having difficulty following your solution.

Currently I have things set up by recording results on a RESULTS sheet and then transferring these to a SCORE SHEET. I copy the names from the results sheet using =results.a3, =results.a6 etc etc as I record results horizontally over 3 lines.

The Score sheet shows Col A Numbers 1 – 38, Col B Names, Col C games Played, Col D wins, Col E draws, Col F losses, Col G shots and Col H points.

You appear to have shared the spreadsheet and in attempting to follow your details I keep getting errors. I wish to rank my results on the Points in Col H with shots in Col G to enable me to automatically sort the results in descending order.

My wish would look like this :

Player Played Points Shots

BROWN, K 20 29 55
SHAW, A 20 20 -6

Can you please set my feet in the right direction.

Thanking you in anticipation.