Comments on: Excel RANK functions with formula examples

When you need to determine the relative position of a number in a list of numbers, the easiest way is to sort the numbers in ascending or descending order. If for some reason sorting is not possible, the Excel RANK function is the perfect tool to do the job. Continue reading →

Comments page 3. Total comments: 162

  1. Hi,
    i need to understand, how formula works and generates percentage number ranking?
    Name Amount Percentage
    majid 20 14.29%
    sajid 70 85.71%
    ahmed 80 100.00%
    sultan 40 71.43%
    khan 30 42.86%
    majid 20 14.29%
    sajid 35 57.14%
    the formula i used =RANK.EQ(B2,$B$2:$B$8,1)/COUNT($B$2:$B$8)
    first one Majid 20 percentage 14.29%. if i divide (20/7*100) is not correct. can you explain the break up of this formula, how works?

    Thanks

  2. Hi,
    Fantastic. But what if i want to assign only 3 rank (1st, 2nd, 3rd)?

  3. If the column for group is starting with a number like 2B, 2E 2C 2S then the ranks for some is +2 or +1 please help

  4. Does any know how to fix the issue with Excel 2022 crashing on startup?

  5. Hi Kevin, try this
    =SUMPRODUCT((A1>=$A$1:$A$10)/COUNTIF($A$1:$A$10,$A$1:$A$10))

  6. Y'all got some tips for ranking without skipping a number yo!

  7. Hello!
    I have 179. Is it possible to rank each subject such that student A will be like position 2/179 or 5/179 or 1/179?
    I really need help. Please??? help me.

  8. HI,I Want to calculate rank for students those who are passed in all subjects only,
    if the passing mark is 7 out of 20.please help

  9. How can I rank on test scores, where anybody scoring over 95% automatically top ranked (1 - ties are ok); and anybody scoring less than 95% is then ranked by total # of test scores?

    Student - Score - Rank
    Billy - 50% - Rank 5
    Ralph - 45% - Rank 6
    Shelly - 96% - Rank 1
    Luis - 95% - Rank 1
    Lucy - 97% - Rank 1
    Shay - 94% - Rank 2
    Monica - 70% - Rank 3
    Sarah - 68% - Rank 4
    Joe - 98% - Rank 1
    Mel - 33% - Rank 10

    1. EDIT:
      Student - Score - Rank
      Billy - 50% - Rank 8
      Ralph - 45% - Rank 9
      Shelly - 96% - Rank 1
      Luis - 95% - Rank 1
      Lucy - 97% - Rank 1
      Shay - 94% - Rank 5
      Monica - 70% - Rank 6
      Sarah - 68% - Rank 7
      Joe - 98% - Rank 1
      Mel - 33% - Rank 10

      1. Hello Juan,
        Thank you for your comment. Here is the formula that should do the trick for you:

        =IF(B2 < 95, COUNTIF($B$2:$B$11, " > "&B2)+COUNTIF($B$2:$B$11, " = "&B2), 1)

  10. Funds 1M 3M 6M YTD 1Y
    Fund1 3% 7% 12% 10% 17%
    Fund2 3% 7% 12% 10% 17%
    Fund3 7% 8% 9% 12% 6%
    Fund4 -5% -11% 22% 14% 23%
    Fund5 10% 16% 14% 15% 26%
    Fund6 0% 3% 19% 19% 20%
    Fund7 -1% 2% 19% 20% 19%
    Fund8 -2% 2% 19% 20% 19%
    Fund9 0% 3% 20% 20% 21%
    Fund10 3% 7% 12% 10% 17%
    Hi Olive, how can we rank data like this with excel formula to find which is the best performing...

  11. How do you prevent the rank from repeating itself...like two values given 1...at the same time

  12. How to rank this by excel formula?

    Carrier POL POD Rate (Dollar) Rank
    Carrier A PGU Jakarta 100 1
    Carrier B PGU Jakarta 200 2
    Carrier C PGU Jakarta 300 3
    Carrier D PGU Jakarta 400 4
    Carrier A PGU Rotterdam 500 4
    Carrier B PGU Rotterdam 400 3
    Carrier C PGU Rotterdam 300 2
    Carrier D PGU Rotterdam 200 1
    Carrier A SGP Jakarta 100 1
    Carrier B SGP Jakarta 200 2
    Carrier C SGP Jakarta 300 3
    Carrier D SGP Jakarta 400 4
    Carrier A SGP Rotterdam 500 4
    Carrier B SGP Rotterdam 400 3
    Carrier C SGP Rotterdam 300 2
    Carrier D SGP Rotterdam 200 1

  13. How ranking group on multiple criteria?
    kindly help to prepare formula

  14. problem of multi conditional ranking. Example. Students got 500,450,400,350,300,250 respectively,Number of their fail subjects r 0,0,1,1,2,2 respectively Their ranks should be as 1,2,3,4,5 ,6. Please help to prepare formula.

  15. How to calculate the students merit so that the highest are on top and lowest in the decreasing order

  16. Excellent! after trying various methods, RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,">"&$C2) really worked for ranking with 4 columns!
    Thanks

  17. I have 20 golfers with all their gross scores from holes 1 through 18 and a total gross ... all that data in a row for each golfer. We pay 4 best gross scores. When there is a tie we go back to each hole starting at hole #1 and look for the lower score(s) which eliminates the other golfers for this payout. Then go to next hole and continue until there is only 1 golfer remaining who gets this payout award for the rank position ($40 for 1st, $30 for 2nd, $20 for 3rd, $10 for 4th). We encountered a situation where 4 golfers had the same GROSS score, on hole #1 2 of them had the same score and better (lower) than the other 2. The tie between these lower 2 finally broke by hole #10; this determined payouts for 1st and 2nd. Of the remaining 2 golfers to determine payout 3 and 4, the tie break came on hole # 5. We did all this manually/visually. Can we get this in xls formulas?? Thank you.
    btw ... tie breaks is a common occurrence in golf so a formula solution would help many tournament organizers.

    1. yes, you can do this through code in excel

  18. Help me how to rank multiple categories. First pass and fail. Second look at mark

  19. Is there any way to do rank where you don't skip?
    For instance
    Score Rank
    30 1
    28 2
    28 2
    27 4

    I would like it so that the 27 is a 3, is this possible?

    1. I found an answer to this...
      =SUMPRODUCT((G2 < G$2:G$27)/COUNTIF(G$2:G$27,G$2:G$27))+1
      Is there any way to do the above based on a value in another column? Let's say column A contains a group number assignment (1,2 and 3).

      1. Mathew Collen, what you are asking about is my current dilemma. Did you ever find an answer?

  20. Hi all, I am trying to get a priority ranked list based on "Impact" (where highest number is higher impact) vs "Effort" (where lowest number is lowest effort), I want to basically get highest "Impact" / low "Effort" items on top of the list. For contest I have speadsheet where the "Impact" rating is in column H (H6 downwards) and "Effort" rating (1 = Low, 2 = Medium, 3 = High, 4 = Very High)is in column I (I6 downwards). I would like a rank of 1st to however many entries we have to be displayed in column EF (EF6 downwards)

  21. Hi
    If the data contains an error and we want to calculate the Rank ABS numbers ascending ignoring ERORR(ex. #value! or #n/a):, how can we do it?
    for example:1,2,#value!,-5, 8, -10, 15,#N/A, 25

  23. Could you help me with a rank formula please? I need to rank negative numbers first descending then positive numbers ascending. The number closest to zero but negative is the highest ranked. The number farthest from zero but positive is the lowest ranked. The closest positive number from zero should be lower in rank than the farthest negative number from zero.

  24. how to calculate percentile rank for employee salary with designation and no of year experienc as Condition

  25. Hello, I am trying to rank data based on a goal range.

    The goal is to score between 450 and 500.

    Currently anyone scoring 375 (as an example) is ranked higher than someone who met the goal falling between 450 and 500.

    Is there a way to set this up?

  26. Thanks alot for your explanations.
    It was very useful
    Keep continue

  27. 1- I want to rank a Rang,
    2- The highest number with Highest Rank
    3- Rank only top 10, rest need to be filled with 0

    Help please.

  28. The Rank.eq function will not allow me to lock the ref array. I can't figure out why this is happening. Do I have to hand enter each and every cell?

    1. Hi Susan,

      To lock the ref range, use absolute cell references with the $ sign, e.g. $A$2:$A$10. To toggle between the reference types, simply select the reference in the formular bar and press F4.

      For more informatiop, please see Absolute and relative cell references in Excel.

  29. Please see the data below where I want to calculate Overall Rank from existing ranked criteria.

    Note that to calculate overall rank, Rank 1 is the main criteria, followed by Rank 2 and 3. Please let me know if you have pre-built formula for this.

    Dcode Rank 1 Rank 2 Rank 3 Overall Rank
    1 50 50 8
    2 50 3 7
    3 49 25 6
    4 47 14 27
    5 45 17 47
    6 37 7 2
    7 33 47 20
    8 31 34 17
    9 30 2 23
    10 25 21 13
    11 23 13 34
    12 16 37 32
    13 14 6 9
    14 12 30 16
    15 10 40 28
    16 9 26 36
    17 8 32 19
    18 5 1 1
    19 4 24 4
    20 3 45 37
    21 1 46 18

  30. This is AWESOME!! I was headed in the right direction logically, but this was the answer to MANY hours of trial and error!!!

    Thank YOU!!

  31. I have a list that is ranked and now I have to give the ranked list a grade (E - Excellent, O - Outstanding, M - Meets Expectations, B - Below Expectations). I can only have 10% of my list be Exceptional, 15% outstanding, 65% Meeting Expectations, and 10% below expectations. I have 34 employees that make up this list. I can manually assign the grades based on this but is there a way to have excel do this for me?

  32. Thank you for these. I haven't used them in a while and this was exactly the refresher I needed. Cheers!

  33. Team Ablebits,
    At the outset thanks for helping people like us.
    Want to rank data with following validations /filters:
    1. Filter on parameters - like order, sales, purchases
    2. Positive and Negative
    3. split the record, for common rank, example in case rank 2 is assigned to 2 records, it should be reassign 2 and 3 number respectively (essentially want unique number for each record)
    many thanks in advance
    regards
    Harsh

  34. Great post, but it is missing another application of ranking. How do I get the rank and take ties in consideration? This is the result I want for example:

    Name Score Rank
    Joe 33 1
    Lisa 31 2
    Kevin 31 2
    Paul 30 3
    Fred 30 3
    Denny 30 3
    Sarah 30 3
    Allen 30 3
    Patrick 30 3
    Anna 29 4
    Johnny 29 4
    Wendy 29 4

    Etc..

    What formula should I use?

  35. I have a hard problem solving these: I had rank it initially based on standard queuing, from there, it will subsequently re rank once the second line be queued.

    sample:

    100 initial rank 1
    200 initial rank 2
    500 initial rank 3

    new input:

    300
    400

    output of final rank:
    100 final rank 1
    200 final rank 2
    300 final rank 3
    400 final rank 4
    500 final rank 5

    please help me

    what should be the best formula to get the final ranking???

    thanks,

    marycris

  36. Great work Svetlana !!

    The formulas work like a charm.

  37. Is there a simple way to automatically sort without using macros.

    Thanks a lot

  38. Thanks for helping me out to calculate Ranking of two pupils that had the same average and the same total but different positions.

    I was able to sort it out through your formula.

  39. how to rank data in another sheet

  40. Dear Sir / Madam,

    Please help me.
    I have 4 Sub category each I'll give 25% ( how to find out Ranking).

    Regards,
    Santosh
    72594 19297

  41. -0.19
    0.18
    0.10
    -0.04
    -0.08
    -0.04
    -2.22
    -0.67

    How to rank this in excel?

    1. Hello,

      Suppose, your original data are in column A. If you place the following formula in B1, for example, you’ll get the result you need:

      =RANK(-0.19,A:A)

      If it doesn't help, then please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help you better.

  42. Dears,
    In RANK.EQ function why rank 2 is skipping to Rank 3 on re applying marks. Could you help me please.

    Best regards,

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)