Calculating Rank in Excel

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

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!

__________

You may also like...

24 Responses

  1. dermotb says:

    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. derek says:

    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. Kemas says:

    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. Jon Peltier says:

    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. Ceci says:

    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. Kemas says:

    =RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1

  7. chip says:

    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.

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

  9. Andrew says:

    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

  10. Oscar says:

    Rank with ties:

    =COUNTIF($B$2:$B$11, “>”&B2)+SUM(IF(B2=$B$2:B2, 1, 0)) + CTRL + SHIFT + ENTER copied down.

  11. Kemas says:

    very good Oscar

  12. Andrew says:

    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 :-)

  13. Deborah says:

    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

  14. Deborah, I’ve uploaded a sample file that shows how you can split the points among 2 or more players with the same rank.
    On the Excel sample files page on the Contextures website, go to the Functions section, and look for FN0019 – Split Points for Shared Rank

  15. Deborah says:

    Thank you very much ! That is exactly what I needed.

  16. ManelR says:

    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.

  17. Geoff_nz says:

    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?

  18. Ralph Brondial says:

    How Do I Stack rank this? thanks

    Teammate survey returns ASAT
    Bryan Alvin Buenaventura 9 100.00%
    Antonio Galido Jr 7 100.00%
    Giselle Ponce 7 100.00%
    Fatima May Bautista 6 100.00%
    Christine Loraine Riveta 5 100.00%
    Dexter Dacalcap 5 100.00%
    Hannah Sharlene Pamarang 4 100.00%
    Lemuel Edner Lazaro Flores 4 100.00%
    Minerva Escamillas 4 100.00%
    Danica Mae Mendoza 3 100.00%
    Gerwin Talagtag Oronce 3 100.00%
    Irish Pontillano 3 100.00%
    Jenrette John Jimenez Fajardo 3 100.00%
    Liana Charmaine Casanova 3 100.00%
    Marry Kate Pillado 3 100.00%
    Adrian Dalao 2 100.00%
    Eljhon Gredes 2 100.00%
    Jeric Oliva 2 100.00%
    Ray Martin Ardales 2 100.00%
    Vincent Ludas 2 100.00%
    Ariel Cabilogan 1 100.00%
    Cheryl Lumanog 1 100.00%
    Claudine Kraus 1 100.00%
    Ferdie Lontoc 1 100.00%
    Jaycel Nacario 1 100.00%
    Jemellee Cabingan 1 100.00%
    Nina Berboso 1 100.00%
    Oneta Tagalo 1 100.00%
    Roxanne Bautista 1 100.00%
    Windy Alindogan 14 100.00%
    Niko De Leon 10 90.00%
    Arman Coronel 6 100.00%
    Joanna Espinosa 9 88.89%
    Aurora Cabral 7 85.71%
    Betric Danien Zamora 5 100.00%
    Maica May Loreto 7 85.71%
    Florence Fatima Coloma 10 90.00%
    Ronesto Bedon 9 88.89%
    Paul Medina 4 75.00%
    Princess Margarette Correche 12 100.00%
    Ma Eunice Rojo 4 100.00%
    Santanina Malsi 4 100.00%
    Shulome Lacbao 7 71.43%
    Emil Martin Picorro 7 100.00%
    Charisse Cababao 5 80.00%
    Pallas Athena Ronquillo 7 71.43%
    James Lui Prado 3 100.00%
    James Oliver Bautista 3 100.00%
    JR Buenafe 3 100.00%
    Ma Jo Ann Cantwell 4 75.00%
    Paul Martin Cabellon 7 71.43%
    Anjo Asenjo 6 83.33%
    Gillrose M Cajepe 6 83.33%
    Eric Cajandab 9 77.78%
    Elena Madrona 8 62.50%
    Love Joy Miranda 2 50.00%
    Mary Jane Arenillo 2 100.00%
    Mary Joyce Sampani 2 100.00%
    Jerissa May Fernando 10 70.00%
    Diane Mula 3 66.67%
    James Alfred Lopez 3 66.67%
    Janine Capawa 3 66.67%
    Matthew Callueng 6 83.33%
    Stephen Rae Macafe 4 50.00%
    Angeli Ampa 5 60.00%
    Mark Kevin Paguio 5 60.00%
    Myrna Abundio 5 60.00%
    Ridonel Aspillaga 5 60.00%
    Dayanara Martos 1 100.00%
    Emmanuel Sumulong 1 100.00%
    Demosthenes Galvan Jr 6 66.67%
    Clifford Almonte 3 66.67%
    Nico Latayan 4 50.00%
    Ruthela Limse 4 50.00%
    Angelica Galang 2 50.00%
    Dennis Castro 2 50.00%
    Hanna Biron 2 50.00%
    Jeilyn Inocelda 2 50.00%
    Masatoshi Manera 2 50.00%
    Noemi Saji 2 50.00%
    Yally Marino 2 50.00%
    April Gerrine Ong 2 50.00%
    Hyatt Laguyo 2 50.00%
    Jan Pangilinan 3 33.33%
    Ma Jenelyn Alipio 3 33.33%
    WevaJayca Cezar 3 33.33%
    John Ian Tagnipez 4 25.00%
    John Michael Pelayo 3 33.33%
    Ismael Magbanua 3 0.00%
    Johnymer Alvarez 3 0.00%
    Jose Mercado 2 0.00%
    Eugene Gogolin 1 0.00%
    Ian Portillo 1 0.00%
    Jusmyn Noriell Merginio 1 0.00%
    Kenneth Martinez 1 0.00%
    Leo Moncawe 1 0.00%
    Marvin Kiunisala 1 0.00%
    Mary Jane Gano 1 0.00%
    Ramera Upod 1 0.00%

  19. PAWAN SHARMA says:

    Dear Sir,

    I want to set vlookup formula with pictures. Is this possible with picture or not. please help.

  20. Eric Eberhart says:

    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)

  21. yuvraj says:

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

  22. Malina says:

    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.

  23. William Hebert says:

    I found another way to resolve ties. In a separate cell:
    =value+RAND()
    The RANK these results.

Leave a Reply to yuvraj Cancel reply

Your email address will not be published. Required fields are marked *