Calculating Rank in Excel

Calculating Rank in Excel

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)

Rank01

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.

Rank02

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 😉

Rank03

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)

Rank04

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.

Rank05

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!
__________

26 thoughts on “Calculating Rank in Excel”

  1. 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.

  2. 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.

  3. 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

  4. 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.

  5. 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)

  6. 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.

  7. 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.

  8. 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 🙂

  9. I hope this is seen. I need to award points based on rank,1st gets 10 pts. 10th gets 1 pt. If there is a tie for 3 & 4 I need to add those points together 6 + 7 and give each team 6.5 pts. I already have the the formula that awards the points correlating to the rank but dont know how to add the IF for a tie. thanks

  10. Hello,

    I’m an Excel newbie and I need to do a golf rank in Excel. Actually I have a table like this one:

    Column A (Player): P1, P2, P3, P4, P5, P6
    Column B (Playes holes): 3, 0, 7, 6, 1, 0
    Column C (Par): +2, 0, -1, -1, +1, 0

    I would like to have a new column D with the rank created with the following rules:

    * Par is better if lower (i.e. -3 is better than +2)
    * If Par is the same we use “played holes” for breaking the tie knowing that greater is better (i.e. P3 and P4 have -1, but P3 has played 7 holes and P4 6 holes, so P3 is up in the rank than P4)
    * Players without played holes go to the end of the list

    So, the rank column will be:

    Column D (Rank): 4, 5, 1, 2, 3, 6

    (i.e. P3 is the first clasified and P6 is the latest one).

    Could this be done dinamically by formulas? Or do I need to program something?

    Thank you very much.

  11. Hi Debbie, I’m stuck to work out code for a variation on the Rank function. The Problem: If we have 7 people with exam results (89%, 78%, 77%, 78%, 66%, 76%& 85%) the normal Rank function would place these 1st(89%), 2nd(85%), 3rd(the 2 @ 78%), and 5th(77%) etc. What I want the code to read is pretty much the same except instead of the 77% result being 5th, I want it to read 4th, and then other results following 5th & 6th (not 7th). Can you help with this one?

  12. Hello All,
    My copy and paste below is not a very good example as the rows and columns are missing. I understand how to use the rank formula in cells that are in the same column and adjacent to each other. All, I am trying to figure out is how to rank cells that are not next to each other. The <<<<<<<<<<< (arrows) below point to the cells that I am trying to add the rank to and the (D9,D16,D24,D30,D35 etc.) are the cells with the information that I am trying to pull the data from. Any help will be greatly appreciated. Thanks, Eric
    Fall Term
    Class Students Passing % Passing Ranking
    Math 1 91 80 =C3/B3 =RANK(D3,$D$3:$D$8)
    Math 2 82 70 =C4/B4 =RANK(D4,$D$3:$D$8)
    Math 3 38 36 =C5/B5 =RANK(D5,$D$3:$D$8)
    Math 4 67 46 =C6/B6 =RANK(D6,$D$3:$D$8)
    Math 5 87 66 =C7/B7 =RANK(D7,$D$3:$D$8)
    Math 6 99 79 =C8/B8 =RANK(D8,$D$3:$D$8)
    Math =SUM(B3:B8) =SUM(C3:C8) =C9/B9 <<<<<<<<<<<<<<<<<< =RANK(D9,D16,D24,D30,D35)
    History 1 118 104 =C10/B10 =E11
    History 2 122 107 =C11/B11 =RANK(D11,$D$10:$D15)
    History 3 125 108 =C12/B12 =RANK(D12,$D$10:$D15)
    History 4 76 69 =C13/B13 =RANK(D13,$D$10:$D15)
    History 5 175 141 =C14/B14 =RANK(D14,$D$10:$D15)
    History 6 111 91 =C15/B15 =RANK(D15,$D$10:$D15)
    History =SUM(B10:B15) =SUM(C10:C15) =C16/B16 <<<<<<<<<<<<<<<<<< =RANK(D9,D16,D24,D30,D35)
    Science 1 135 120 =C17/B17 =RANK(D17,$D$17:$D23)
    Science 2 103 83 =C18/B18 =RANK(D18,$D$17:$D23)
    Science 3 1 0 =C19/B19 =RANK(D19,$D$17:$D23)
    Science 4 137 118 =C20/B20 =RANK(D20,$D$17:$D23)
    Science 5 84 71 =C21/B21 =RANK(D21,$D$17:$D23)
    Science 6 145 116 =C22/B22 =RANK(D22,$D$17:$D23)
    Science 7 103 82 =C23/B23 =RANK(D23,$D$17:$D23)
    Science =SUM(B17:B23) =SUM(C17:C23) =C24/B24 <<<<<<<<<<<<<<<<<< =RANK(D9,D16,D24,D30,D35)
    Social Studies 1 57 43 =C25/B25 =RANK(D25,$D25:$D29)
    Social Studies 2 62 50 =C26/B26 =RANK(D26,$D25:$D29)
    Social Studies 3 53 43 =C27/B27 =RANK(D27,$D25:$D29)
    Social Studies 4 179 128 =C28/B28 =RANK(D28,$D25:$D29)
    Social Studies 5 84 76 =C29/B29 =RANK(D29,$D25:$D29)
    Social Studies =SUM(B25:B29) =SUM(C25:C29) =C30/B30 <<<<<<<<<<<<<<<<<< =RANK(D9,D16,D24,D30,D35)
    P.E. 1 126 97 =C31/B31 =RANK(D31,$D$31:$D34)
    P.E. 2 89 67 =C32/B32 =RANK(D32,$D$31:$D34)
    P.E. 3 91 73 =C33/B33 =RANK(D33,$D$31:$D34)
    P.E. 4 84 60 =C34/B34 =RANK(D34,$D$31:$D34)
    P.E. =SUM(B31:B34) =SUM(C31:C34) =C35/B35 <<<<<<<<<<<<<<<<<< =RANK(D9,D16,D24,D30,D35)

  13. in my exl i put rank formula but some digit is same than rank formula is provide me twice rank,so please help..

  14. Ties are okay for me but I need the rank to be accurate. So if I have a tie for 1st. I need the next best time or score to show as 2nd place not 3rd. Any suggestions.

  15. How do i get their position NAME:JOHN,GIDEON,EMMY. SUBJECT:BIOLOGY,PHYSICS,MATHS SCORES OF JOHN:80,90,88…. SCORES OF GIDEON:90,98,77….SCORES OF EMMY:80,80,80

  16. How to find the which was the Next Percentage if it is 1st Rank next column should the 2nd Rank Percentage.
    Day Difference Rank Next Marks
    31 4 5
    5 5 Last
    36 3 31
    44 2 36
    392 1 44

  17. Hi, I would like to make excel table for ordinary tennis league, but if 2 teams are equal with points than it should take into account their mutual match, so who won the match is higher ranking.
    So, I have 12 teams, namely:
    Team 1
    Team2
    Team3
    Team4
    Team5
    Team6
    Team7
    Team8
    Team9
    Team10
    Team11

    After entering the results for each round, let Excel itself sort the Teams in the following order:
    a) Team with most points is highest ranking.
    b) If 2 teams with the same nr. of points, the head-to-head is then watched and whoever won is higher ranking. If the teams have not yet played each other or have result for draw, then skip to c).
    c) If the teams are still the same, then they are judged by goal difference – higher difference is higher ranking.
    d) If the teams are still the same, then see who received fewer goals – who received less goals is higher ranking..
    e If the teams are still the same, then see who scored more goals – who scored more goals is higher ranking.
    f) If the teams are still the same, then they occupy the same ranking place.

    Here is link for demo file:
    https://docs.google.com/spreadsheets/d/1MvQz0USGOsceyaHEFGpgTNRPL9hxbGGR/edit?rtpof=true&sd=true

    If you have any solution / formula, pls let me know

    Regards

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.