How to create a ranked list

[Guest]

Hello

I have a scorecard for the Eurovision Song Contest tomorrow. This scores
24 countries (listed in A1:A24), giving each country a point score between 1
and 20 (points are in B1:B24).

I can use the rank function in column C to determine which country has come
first in the list (i.e. in C1, I put =rank(B1,$B$1:$B$24) and copy the
formula down to C24).

This then allows me to create a new list, with the countries in order from
who has scored most points to who has scored least. I do this by repeating
the countries in column D, and then in column F I use the formula
=vlookup(row(F1)-row($F$1)+1,$C$1$24,2,FALSE).

This works ok as long as two countries do not score the same number of
points. If this happens, then I get an "N/A" in this column. How do I get
around this? And can I make the formula further fool-proff so that if 3, 4,
5 or 6 countries score equal points, the formula doesn't fall over?

If 2 or more countries are tied, the order I would like Excel to return the
countries in could be either (i) the order in which the country appears in
the original list; or (ii) alphabetical.

Many Thanks for any help you can give me.

 

[Guest]

There are examples here

http://www.cpearson.com/excel/rank.htm


Regards,

Peo Sjoblom

 

[Guest]

You might also wish to try this non-array formulas construct ..

With countries listed in A1:A24, points in B1:B24

Clear your existing col C first, then place:
In C1: =RANK(E1,$E$1:$E$24)
In D1: =INDEX(A:A,MATCH(LARGE($F:$F,ROW(A1)),$F:$F,0))
Copy D1 to E1
In F1: =IF(B1="","",B1-ROW()/10^10)
Select C1:F1, copy down to F24

Cols D & E will return a full descending sort of the 24 countries in col A,
sorted by their points in col B. Countries with tied scores, if any, will
appear in the same relative order that they are listed in col A. Col C
provides the ranking of the countries (Hide away the helper col F if necess).