will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Calculating Rank in Excel
This article was going to be about sorting in Excel, because Jim Cone just sent me the latest version of his Special Sort add-in. So, I hauled one of the big, dusty Excel books off my shelf, to see if there were any scintillating sorting secrets to uncover. Under Sorting, I saw “rank calculations” so I turned to that page.
The referenced page explained the RANK function, and included this warning, “Be sure the data set is sorted in either ascending or descending order.” Hmmm…I’d never heard that before, and a quick check in Excel’s Help proved that statement was wrong – the data set does NOT have to be sorted. Whew! I’ve been doing it right all along.
Do You Use the RANK Function?
Maybe you use the RANK function every day, but I rarely need it. If I want to see which products have the highest prices, or which students have the best scores, I’d probably just sort the list.
But now that I was in Excel Help anyway, I decided to learn a bit more about the RANK function. Maybe it has exciting features and hidden tricks to discover. (No, I hadn’t been drinking, I’m just optimistic.)
RANK Function Basics
If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order.
For example, here’s a list of 10 student test scores, in cells B2:B11. To find the rank of the score in cell B2, enter this formula in cell C2:
=RANK(B2,$B$2:$B$11)
There are 3 arguments for the RANK function:
- number: in this example, the number to rank is in cell B2
- ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. I used an absolute reference, so the referenced range will stay the same when we copy the formula down to the cells below
- order: (optional) Use zero, or leave this argument empty, to find the rank in the list in descending order. For ascending order, type a 1, or any other number except zero. I left this blank, to find the rand in descending order. If you were comparing golf scores, you could type a 1, to rank in ascending order.
I copied the formula down to cell C11, and the scores were ranked in descending order, as promised.
RANK Function With Ties
What happens to the ranking if some of the scores are tied? I was going to use the Olympics as an example, but it looks like every sport has different rules, so that won’t work!
In our example, if I change cell B7 to 43, it’s tied with cell B3. Both cells are now ranked as 2, and it doesn’t affect any of the other rankings. The score of 32 is still 4th, not 3rd, because there are 3 scores ahead of it.
So, if you were handing out awards to the top students,
- the score of 45 would get the first place blue ribbon
- the two students with 43 would each get a second place red ribbon
- no one would get a third place ribbon (green? white?)
- everyone else gets one of those orange “Participant” ribbons
Breaking Ties With the RANK Function
In some cases, ties aren’t allowed, so you have to find a way to break the tie. We could keep track of the number of minutes that each student worked on the test, and use that time to break any ties.
I added the Test Times in column A, and a TieBreak formula in column D.
=IF(COUNTIF($B$2:$B$11,B2)>1,RANK(A2,$A$2:$A$11,1)/100,0)
The TieBreak formula checks to see if there’s more than one instance of the number in the entire list ($B$2:$B$11).
- If there is more than one instance, it ranks the Times in ascending order and divides that by 100, to get a decimal amount. Note: The divisor, 100, could be changed to another number, if you were working with a longer list.
- If there is only one instance, the result is zero.
Finally, you can combine the RANK function results with the TieBreak results, to get the final ranking.
How Would You Break the Ties?
I’m sure there are other ways to break the ties, so if you use something different, please mention it in the comments. Thanks!
__________
March 22nd, 2010 at 12:46 am
To prevent ties, I include a column at left to number the rows 1,2,3,4,...., and I add this number to the scores (divided by a large number like 100,000). This guarantees each number is unique.
March 22nd, 2010 at 5:48 am
A combination of RANK and lookup functions is very useful when you have a large list and the boss wants to see the current top ten headaches in the weekly report. Instead of sorting the list and copy-pasting the top ten out every week, you can use RANK to establish the top ten values, and lookup to pull out the other details of the cases.
But tie-breaking is essential if you don't want Joe Bloggs counted twice in the list, and Jane Smith missed out, because you looked up using a duplicate value.
March 22nd, 2010 at 8:46 am
I use a variation of Dermot's approach. This ensures that ties retain their relative order when ranked. In the approach you used, the lower tied value has a higher rank than the upper tied value, thus reversed in the ranked list.
March 22nd, 2010 at 9:04 am
I experienced this problem with Rank last week, and I combined it with IF. My data range was F3:F22, and I used, i.e. on F3 ranking =IF(RANK(F3,$F$3:$F$22,1)-COUNTIF($F$3:$F$22,0)<0,1,RANK(F3,$F$3:$F$22,1)-COUNTIF($F$3:$F$22,0)+1)
March 22nd, 2010 at 11:20 am
we must make small change in list
list b2:b10
the new list in a2 : a10 =
=B2+ROW(A2)/10000000
after that we use rank function on new list
I'm waiting a better than this
Thanks
March 22nd, 2010 at 2:47 pm
Using the row number to break ties seems kind of arbitrary--too bad for the kid who happened to have his information added later--he's #3 by default! (Unless, of course, the tests are entered in the order that the student's completed them, and the earlier order actually indicates an earlier completion).
I like Debra's approach of another criterion to add to the score, and I use that approach myself. Only thing I'd do is do one final RANK on the last column of data to provide rankings of the final score. I might also just add the time to the tests in advance (I'd have to collect them all anyway) and that way I just have to rank once.
March 22nd, 2010 at 3:34 pm
=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1
March 22nd, 2010 at 8:07 pm
Thanks for your comments and formulas, and @derek, thanks for explaining how you use RANK in your weekly reports.
@chip, I like your idea of adding the test score and test time (as a small decimal number?) before doing the ranking.
March 23rd, 2010 at 3:31 pm
Rank with ties:
=COUNTIF($B$2:$B$11, ">"&B2)+SUM(IF(B2=$B$2:B2, 1, 0)) + CTRL + SHIFT + ENTER copied down.
March 23rd, 2010 at 6:24 pm
Hi Debra,
This is my way to get around the duplicate issue. Have not used it for a while but it seems to work
http://blog.livedoor.jp/andrewe/archives/6901749.html
March 23rd, 2010 at 6:42 pm
very good Oscar
March 23rd, 2010 at 8:37 pm
Silly me, I didn't read the entire post.
What I intended is to show a way to get the right ranking. Both scores of 43 are ranked 2 and 32 is ranked 4. But it should really be ranked 3. Same for the other numbers under 43.
Anyway, like the tie-breaker solutions