To do some research on sorting, 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, to see what it said about calculating rank in Excel.
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 in the ranking.
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.
Get the Excel File
For more details on breaking ties, and to get the sample file, go to the Excel Rank with Ties page on my Contextures site. The zipped Excel file is in xlsx format, and does not contain any macros.
And for more RANK formula examples, and to get the sample Excel file, go to the RANK formula in Excel, page on my Contextures site.
__________
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.
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.
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
Rank with ties:
=COUNTIF($B$2:$B$11, “>”&B2)+SUM(IF(B2=$B$2:B2, 1, 0)) + CTRL + SHIFT + ENTER copied down.
very good Oscar
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 🙂