Sort of scores

QuestionEdit

QUESTION: I run a pool league, and I useto produce schedules and standings, I had figured most of my formulas need it for this, but I don't know how to automatically "SORT" the team positions after filling the scores, what I do I copy and paste the team chart that shows the position the I use sort using the points column, I saw ansheet for the 2010 World Cup that does the automatic sorting from first place to last. Can you assist me with this issue? I really appreciate any help om this matter, I

ANSWER: I believe they are using (in hidden cells) the RANK function

First use Rank to "rank" those points, lets say you have 20 players and all scores are in column A, the your formula for the first player is: =RANK(A1,$A$1:$A$20,0) where 0 means the one with most of the points is the leader. Otherwise, use 1

now that you have all of them ranked, you can set up a table and manually type in positions: 1 2 3 4 5 ... 20

then you just need to perform a vlookup to the original table to put the name beside the 1rst, 2nd... And 20th place.

---------- FOLLOW-UP ----------

QUESTION: Thanks! this really work, but I have 2 teams that have the same wins and one of the vlook shows #N/A, how can I fix this?

AnswerEdit

that's because when there is a tie the formula will return the same name, and then it will miss the following number.

You have to untie those two teams using an extra criteria. (for example: like in soccer when there is a tie in point they had the goal avg)

There is no built in function for multiple criteria ranking

But, I am happy to tell you there is a work around

use the following function instead of the previous one: =RANK(A1,A$1:A$20)+SUMPRODUCT(--(A1=A$1:A$20),--(B1<B$1:B$20)) now you have the column b range, which is the second criteria

try it and let me know