generating rank-ordered list
Ok, here's a few ways to do it:
Assume names are in A1:A10, scores in B1:B10.
If you want to take the chance that no ties will occur:
Enter this formula in, say, C1:
=INDEX(A$1:A$10,MATCH(LARGE(B$1:B$10,ROWS($1:1)),B $1:B$10,0))
Copy down to C10.
If you want to account for possible ties:
One way is to use a helper column to rank the scores:
Enter this formula in C1 to rank the scores. Copy down to C10:
=RANK(B1,B$1:B$10)+COUNTIF(B$1:B1,B1)-1
Then, to get the names in ranked order, say, in D1:
=INDEX(A$1:A$10,MATCH(SMALL(C$1:C$10,ROWS($1:1)),C $1:C$10,0))
Copy down to D10.
If you want to account for possible ties and not use the helper rank column:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER) in C1:
=INDEX(A$1:A$10,MATCH(LARGE(B$1:B$10-ROW(B$1:B$10)/10^10,ROWS($1:1)),B$1:B$10-ROW(B$1:B$10)/10^10,0))
Copy down to C10.
Biff
wrote in message
ups.com...
yes, but they are very unlikely
Biff wrote:
Are there any tie scores or are tie scores even a possibility?
Joe - 100
Sue - 110
Tom - 100
Biff
wrote in message
oups.com...
I have an alphabetical list of names and scores associated with them.
I would like to generate a list with these names in order of their
scores, but I can't seem to find how this is possible. I recently
found use of the "lookup" function, but it requires that the
"Lookup_vector" have data already sorted. Is there some way to take
care of this?
|