Results 1 to 4 of 4
2010-01-31, 15:09 #1
Using Excel 2007, I've got 20 "candidate" cells that have a number in them between 1 and 1000. I need to create "ranking cells" next to each of those 20 candidate cells that will display a ranking for each cell relative to the others. The candidate with the highest value will have a ranking of number 1, the candidate with lowest value will be number 20.
In other words, if one of the candidates has a value of 1000, I want the "ranking cell" next to it to show the number "1". The candidate that has the next highest value, say perhaps 950, I want the "ranking cell" next to it to show the number "2". The candidate with the lowest value will have a ranking of 20. If two of the candidates have the same number, then I want them to rank equally, and there will be no number 20 in that case.
Can anyone tell me how to do this?
2010-01-31, 15:19 #2
- Join Date
- Dec 2009
- Mexico City, D.F., Mexico
- Thanked 0 Times in 0 Posts
The function RANK(value, range of values) corresponds to your needs, I guess. If two candidates have the same score, they will be ranked equally and the next one will be ranked with a rank +2 (12, 13, 14, 15, 15, 17, 18, ...) which I think is a more suitable behaviour.This eco-post is made of recycled electrons
2010-01-31, 17:21 #3
- Join Date
- Apr 2001
- Cambridge, UK
- Thanked 3 Times in 3 Posts
This is true.
Rank also has a 3rd parameter which can be 0 or 1
e'g. RANK(CELL, RANGE,ORDER)
If ORDER = 0 or is omitted then the LARGEST number will be ranked 1
If ORDER = 1 then the LOWEST number will be ranked 1Andrew
2010-02-02, 07:55 #4
OK thanks to both of you. I will give this a try.