Rank equal values in a table

Highlight both columns of data and sort on the second column using Date/Sort - just as crxmail has suggested. Then in the ranking column put a 1 in the first cell and the following formula in the second cell of the ranking column:

=IF(C2<>C1,A1+1,A1)

copy it down the column and it will only increment the ranking if there is a change of value in column C.

I thought about using the sort tool, but there's no way to use that automaticly, is there? I meen with the help of worksheet functions?

mats

If the data will always be located in the same place (after an import from another sheet or workbook) in the worksheet, you can consider recording a macro that will sort the list in the specified cell range.

The ranking values can be done with the formula posted by patrickab, however would need to be adapted if you have 3 or more identical values.

Hi hucklberry,
Consider using the RANK function. It will assign the same rank to tie scores, then skip as many subsequent positions as there were ties. In other words, the ranking might be 1, 2, 3, 3, 5, 5, 5, 8, 9, 10
To rank scores in column A:
=RANK(A1, A$1:A$10)      Ranks scores in ascending order
=RANK(A1, A$1:A$10,1)    Ranks scores in descending order

Cheers!

Brad

Thanks Brad, I didn't know about the RANK- function, and it could be useful, but my problem is that I don't know how to keep track of the reference to n:th largest value, if there are two or more equal values. I need to use the reference to pick the corresponding name from the name- column.
Maybe its easier to wright some VBA code using the sort- method in the Workbook_SheetCalculate sub or something like that....

mats

crxmail - Actually there is no need to adapt my formula as it will deal with as many tie scores as there are. The reason for this is that it only compares the previous cell - it doesn't go back to cell A1. However if there are three tie scores of 2nd rank and you want the next rank to be 5th then =RANK() should do that.

hucklberry - Surely by sorting the scores in the first place you will obtain the scores in descending order - if that is what you have selected in Sort. The nth largest will be still be sorted into its correct position irrespective of how many tie scores there are. If you want the tie scores names to be sorted at the same time as the scores so that tie score names are in alphabetical order then you can select a second column on which to Sort. I have a suspicion that you perceive it as more complex because you might be creating another set of data instead of just Sorting the original set of data.

Re-reading your question and your most recent response suggests to me that you need both =RANK() as well as position of the nth largest score. If that is the case then I believe you should use =RANK() in one column and my =IF(C2<>C1,A1+1,A1) in another column. My formula will quickly get out of step with =RANK() as soon as there are tie scores. However mine will tell you the nth highest score and =RANK() will tell you its position in the hierarchy of scores.

Have a look at the file you can find at the following link:

http://www.asdy88.dsl.pipex.com/Experts%20Exchange/hucklberry.xls

I have used your data but sorted it on both the Score key (descending order) and the Name key (ascending order). The =RANK() and my formula give what I have described already.

Does this do what you are looking for?

Patrick

hucklberry,
In Chip Pearson's OFFSET formula, the ROW()-0 is returning the numbers 1, 2, 3, 4, 5 ...   The formula subtracts 0 in case the first cell containing the formula is not in row 1--just change the 0 to the first row number minus 1.
Brad

Thanks Brad!
The trick with the "countif" mixed with "rank" was a perfect solution to my problem!

Thanks to patrickab as well, but with Brad's answer I didn't have to do the sorting part, and the automatic ranking was what I wanted to have.

rgds
mats