Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
generating rank-ordered list
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
generating rank-ordered list
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
generating rank-ordered list
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
generating rank-ordered list
Hi Jeffrey,
Say your names are in column D and scores are in column E. Highlight both columns of data Goto DataSort Select sort by column E from the pulldown box Check descending Click OK Any ties will retain their alphabetical order. HTH Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a List based on your choice from Another List | Excel Discussion (Misc queries) | |||
Generating list | Excel Worksheet Functions | |||
How do you find the list option in excel on office xp | Excel Worksheet Functions | |||
list generating | Excel Worksheet Functions | |||
Refresh a Validation List? | Excel Discussion (Misc queries) |