Rank within Rank

[excelnewbieian]

Ok...

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

 

[Bob Phillips]

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]

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 .
Dont worry I know you were just testing me!

Thanks again Bob.

 

[Bob Phillips]

"excelnewbieian"

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 .
Dont worry I know you were just testing me!

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

 

[IMcN]

"excelnewbieian"
(e-mail address removed) wrote in
message

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