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 2. Total comments: 157

  1. Topic - How to get N largest or smallest values

    Student Score
    Daniela 79
    Tommy 79
    Edward 90
    Julia 88
    Timothy 89
    Peter 75

    Bottom 3
    Score Name
    1 75 Peter
    2 79 Daniela
    3 79 Daniela

    79 Score will be repeated name, it is not taking next name in Index Formula. How to correct it.

  2. Hi there,

    I have used the rank eq function to rank the number of stores as per their sales values. What I'm trying to do is allocate stock to these stores. I have applied the logic but there is remainder stock that I want to be able to allocate to the top 10 ranked stores. For example if I have 200 units left, I want to be able to allocate 10 out of this 200 to each of the top 10 stores in a loop. 1st store gets 10, then 2nd, then 3rd till the time 200 becomes zero. HELP!!

    Cheers

    1. Hello!
      I don't know how you determine the rank, but a formula with an IF function might look like this:

      =IF(RANK($A2,$A$2:$A$100) < 11,B2+200*0.1,B2)

      I hope it’ll be helpful.

  3. Hi

    I am wondering if there is a formula I can use to show the ranking slightly differently to how this formula is working. In the example below, there are 18 records and ideally I would like the first three to show 18, then then the next two as 15, then the next three as 13 and so forth. The way it is working currently is that it is ordering up from 1, so if has four values as 1, then the next group as 5. So I am looking for similar logic, but having the ranking start from the higher numbers, not the lower numbers. If this is not clear, please let me know.

    Best result adjusted Sum Product Rank
    -0.95 16
    -0.95 16
    -0.95 16
    -0.9 14
    -0.9 14
    -0.85 11
    -0.85 11
    -0.85 11
    -0.8 8
    -0.8 8
    -0.8 8
    -0.75 5
    -0.75 5
    -0.75 5
    -0.7 1
    -0.7 1
    -0.7 1
    -0.7 1

    1. Hello!
      Use the RANK.EQ function, a detailed description of which you can see in the article above.

      =RANK.EQ(A2,$A$2:$A$19,0)

      Сopy this formula down along the column.

  4. Hello - I am trying to rank race times and then ultimately give the ranked position a point value. For example there are 3 competitors with times: 12.8, 13.5, 10.1 and I want to rank them with 1st, 2nd, 3rd or 1,2,3; then give 1st = 10 pts, 2nd= 9 pts, 3rd = 8 pts. What formula would I use or how would I go about getting to the ultimate place of points? I made a column with the ranking using this formula I found: =IF($B4=0,"",IF($B4>0,RANK($B4,$B$4:$B$22,1) - COUNTIF($B$4:$B$22,0), RANK($B4,$B$4:$B$22,1)))

    But now I need to create the points associated with the rank. Thank you. Bella

    1. Hi!
      To determine the scores you can use the formula -

      =11-RANK(A1,$A$1:$A$3,1)

      Hope this is what you need.

  5. Hello friends. Please I want to rank my students whose individual records are contained in different sheets of same workbook and know who comes 1st, 2nd 3rd....etc. can't seem to solve it. Please help.

  6. I have positive and negative values for planet strength for 9 planets..I want Ranking considering both positive and negative values..
    Kethu 865 420
    Jupiter 1464 551
    Rahu 1389 666
    Mercury 1282 840
    Venus 923 385
    Sun 728 460
    Moon 1200 611
    Mars 1293 596
    Saturn 1406 556
    Rank is required..Kindly advise..

    1. Hello!
      If I understand your task correctly, pay attention to the paragraph in this article - How to calculate rank in Excel by absolute value

  7. Hello, I tried to apply the formula which worked for rounded values but doesnt seem to work with others :

    It worked with this list :
    66
    94
    77
    99
    106
    82
    113
    75
    90
    96
    65
    95

    But not with these values :
    Turnover £1m

    20.5
    26.9
    4.6
    47.8
    9
    37.1
    19.8
    31.5
    28
    22.5
    28.9
    21.2
    19
    24.7
    38.9
    41.5
    14
    22.7
    28.9
    12.3

    Is there another formula? Cause it keeps on saying NO VALUE when I try to do the RANK.AVG formula
    Thank you very much

    1. Hello!
      I cannot guess which formula you are using. If all of your values in the list are numbers, then the RANK.AVG function works correctly.

  8. i am facing a problem. problem is that i have a marks sheet of students in which mostly we have same mark of students. i need same position against of same marks and next highest position should be second but that shows third instead of 2nd.... need help

    1. Hello!
      To determine the rank in a list with duplicate values, use the formula

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

      This should solve your task.

      1. not working sir.... let me give you example

        obtain marks Perc% Rank expecting
        419 99.76 1 1
        419 99.76 1 1
        418.5 99.64 3 2
        418.5 99.64 3 2
        414 98.57 5 3

        i need rank as 1-1-2-2-3

        1. Hi!
          To determine the rank in a list with duplicate values, use the formula:

          =COUNT(1/FREQUENCY($A$2:$A$15,IF($A$2:$A$15>=A2,$A$2:$A$15)))

          In Excel2019 and below, you must enter as an array formula.

  9. Hi,

    This is excellent thanks for sharing, is there a way to modify the option "How to calculate rank in Excel by absolute value" to be based on another criteria, e.g. rank absolute value by department (I have blanks in the data which need lowest value given and then the lowest actual number with a 2 etc)?

    Any guidance which would hugely appreciated

    BW
    Mark

      1. Hi,

        Thanks for the resposne.

        I have tried this and my data has ties and blanks within it, using the option you suggested doesn't provide a dense rank unfortunately. It skips values when there is a tie.

        Is there a way of modifying it so a tie would return the same value, then the next would increment by 1 (with zero always being the lowest returned value as I'm raking in descending order)?

        Thanks I really appreciate your assistance

        BW
        Mark

        1. Sorry I meant to state 1 as the lowest rank returned, which blanks should be allocated and then a 1 to the lowest number that isn't blank :-)

          1. Hi!
            I could not find the problems you are talking about. The same results do not affect the ranking. The resulting ranks are, for example, 1, 1, and 3. Clarify your problem.

            1. Thanks for coming back to me, I've tried two formulas to do this including your suggestion which is appreciated.

              Say I have a set up results as follows, what I need is for the rank not to skip a value in a tie situation (dense rank).

              So the top rows would read, 1, 2, 2, 3, 3, 4, 4 as opposed to 1, 2, 2, 4, 4, 6, 6

              race_id race_rpr RPR Rank2 RPR Rank 3
              788256 74 1 1
              788256 73 2 2
              788256 73 2 2
              788256 72 4 4
              788256 72 4 4
              788256 71 6 6
              788256 71 6 6
              788256 70 8 8
              788256 70 8 8
              788256 70 8 8
              788256 69 11 11
              788256 69 11 11
              788256 68 13 13
              788256 67 14 14
              788256 63 15 15

              Hopefully this makes sense, the fomulas I'm using are in a table, both are delivering the same result

              RPR Rank 1 =SUMPRODUCT(($B2=[race_id])*(W2"&[@[race_rpr]],[race_id],[@[race_id]])+1

              1. I tried on Excel 365 and Windows Excel (current version). The formula seems to only be returning zeros?
                =COUNT(1/FREQUENCY($C$1:$C$18*($B$1:$B$18=B1),IF($C$1:$C$18>=C1,$C$1:$C$18)))

              3. Hmm, that is odd. I'm on Mac desktop client 16.61.1. It seems to behave the same as PC with Arrays. I prefer PC but maybe I should check on my other computer.

              4. Hmm, not sure If I'm just not getting this. I tried the formula you specified,
                =COUNT(1/FREQUENCY($C$1:$C$18*($B$1:$B$18=B1),IF($C$1:$C$18>=C1,$C$1:$C$18)))
                in cell E1 and ctrl+shift+entered then dragged down but it is returning zeros for all. I wasn't sure if the issue was in the Bill Number being a string instead of value so tried with value only but it still was returning all zeros. Do I not need a SUMPRODUCT in there somwhere?

              6. Sorry if I was unclear, so column D is what I want it to return. What I'm after is a suffix for the bill number. So ultimately it would return something like INV1234-1, INV1234-1, INV1234-3, INV1234-1 & INV1234-2 for that bill number. So column D is the rank of distinct PO_NUMBER for each BILL_NUMBER. For INV1234, there are 3 distinct PO_NUMBERs (300004, 300005, 300014), thus the suffixes are -1, -2 & -3.

                The reason for this is because we are converting POs to Vendor Invoices (Bill), A single Bill is matched to 3 separate POs and we can't convert the 3 POs to one Invoice. We also can't create three invoices with the same exact invoice number. We have to convert the 3 lines on PO 300004 to a vendor invoice with bill number INV1234-1. Then convert the 1 line on PO 300005 to vendor invoice INV1234-2. Then convert the 1 line on PO 300014 to vendor invoice INV1234-3.

              7. Hi!
                This formula determines the rank by the condition - the same values in column B.

                =COUNT(1/FREQUENCY($C$1:$C$18*($B$1:$B$18=B1),IF($C$1:$C$18>=C1,$C$1:$C$18)))

                Highest rank at maximum values. The same values have the same rank.

              8. Alexander, I was trying to repurpose your SUMPRODUCT((FREQUENCY piece for my application but I'm not quite getting what I'm after. I feel like arrays have always been my weakest area in Excel. Here's my table. Column D is what I'm after, column E is what my poorly reworked array is returning. Cell E2 is this and is dragged down for the rest of column E. What am I doing wrong?

                P.S. I'm a fan of yours and Svetlana's. I consider myself now a pro in Excel for the most part but occasionally I get stumped. Sure enough, after a quick google search, I always end up at one of your articles which clears things up for me. This has happened consistently over the past 10 years. Pass on my thanks for that!

                In cell E2
                {=SUMPRODUCT((FREQUENCY(IF($B$1:$B$19=B2,$C$1:$C$19),$C$1:$C$19)>0)*($C$1:$C$19<C2))+1}

                RECORD;BILL_NUMBER;PO_NUMBER;SHOULD_RETURN;RETURNS_THIS
                1;B5678;300006;1;1
                2;B5678;300006;1;1
                3;INV1234;300004;1;1
                4;INV1234;300004;1;1
                5;INV1234;300014;3;4
                6;INV1234;300004;1;1
                7;INV1234;300005;2;3
                8;X5555;300009;1;2
                9;INV1240;300001;1;1
                10;INV1240;300001;1;1
                11;INV1240;300002;2;2
                12;5678910;300012;2;2
                13;5678910;300011;1;2
                14;5678910;300011;1;2
                15;B5700;300013;1;2
                16;I4529;300008;2;1
                17;I4529;300007;1;1
                18;I4529;300007;1;1

              10. Hello!
                To calculate the dense rating for a group, use the formula

                =SUMPRODUCT((FREQUENCY(IF($A$1:$A$15=A1,$B$1:$B$15),$B$1:$B$15)>0)*($B$1:$B$16>=B1))

                This is an array formula. Press Ctrl + Shift + Enter so that array function works.

  10. I have data for which I have calculated their mean, median, mode, standard deviation. how do I calculate their rank? should I only use mean (highest mean would be rank 1 and so on ) or should I use ''mean divided by standard deviation, i.e. signal to noise ratio (snr) '' to calculate their rank in excel? please help!
    this is the data that I need help with.
    MEAN SD
    4.4 0.753937035
    4.45 0.944513241
    4.05 1.145931017
    4.75 0.550119604
    4.5 0.827170192
    4.35 0.74515982
    4.45 0.887041208
    4.05 0.944513241

  11. I have data for which I have calculated their mean, median, mode, standard deviation. how do I calculate their rank ? should I only use mean (highest mean would be rank 1 and so on ) or should I use ''mean divided by standard deviation, i.e. signal to noise ratio (snr) '' to calculate their rank in excel? please help!
    this is the data that I need help with.
    MEAN SD
    4.4 0.753937035
    4.45 0.944513241
    4.05 1.145931017
    4.75 0.550119604
    4.5 0.827170192
    4.35 0.74515982
    4.45 0.887041208
    4.05 0.944513241

  12. Hi, I have a set of numbers:
    0,0591
    0,0556
    0,1163
    -5,7416
    0,8221
    -0,5554
    1,0672
    1,8117
    0,8832
    0
    0,2268
    1,2297
    1,5574
    0,6483
    6,2281
    2,4252
    2,8033
    5,7375
    1,8332
    0,7603
    1,7136
    0,2329

    I need to rank them closest to 0, with 0 being ranked 1, that's an easy part. But I need to treat the negative numbers as being the last on the ranking order.
    Thank You in advance!

    1. Hello!
      Sorry, I do not fully understand the task. What does the “I need to rank them closest to 0, with 0 being ranked 1” phrase mean? Give an example of the expected result.

      1. Hi, sorry for the confusion. Here's my task:

        List Rank Rank Needed
        0,0556 4 2
        0,1163 6 4
        0,8221 11 9
        0,0591 5 3
        0,4831 9 7
        -5,7416 1 14
        1,0672 13 11
        0,7626 10 8
        1,8117 14 12
        0,8832 12 10
        0 3 1
        0,3405 8 6
        0,2268 7 5
        -0,5554 2 13

        I use this formula, to begin with, RANK(A2;$A$2:$A$15;1), to start the list with 0 i use this: IF(A2>=0;RANK(A2;$A$2:$A$15;1)-COUNTIF($A$2:$A$15;"<0")). My question is how do I add the negative numbers to the back of my ranking list?

        1. Hello!
          If I got you right, the formula below will help you with your task:

          =IF(A1>=0,COUNTIF($A$1:$A$14,">=0") - COUNTIF($A$1:$A$14,">"&$A1), COUNTIF($A$1:$A$14,"<0") - COUNTIF($A$1:$A$14,"<"&$A1) + COUNTIF($A$1:$A$14,">=0"))

          Pay attention to the following paragraph of the article above — Rank positive and negative numbers separately

          1. Perfect! ?

  13. I believe in the last message I sent you regarding Ranking on Every Nth Column my formula showed E2 for the search value, it should have been F2 since the formula is searching on every other other column (Modulus 2). The formula should have been as below. This still get the SPILL error. I'd greatly appreciate if you had a solution to this. Thanks.

    =RANK.EQ(F2,IF(MOD(COLUMN($A2:$J2)-COLUMN($A2)+1,2)=0,$A2:$J2))

    1. Hello!
      #SPILL! is a new Excel error that only occurs when a dynamic array hasn't enough place to display the result. There should be enough blank cells around your formula for it to write the results of the calculations. If these cells are occupied by formulas or values, an error will occur.

      For more information, please see Excel #SPILL! error - causes and fixes.

  14. Hi Folks,
    I use the Max of every nth Column using an approach from ExcelJet in the link below . When I try this with the RANK or RANK.EQ functions, I get a spill error. for example using the formula where the array of values in columns A2 though K2, and attempting to get the rank of cell E2 from every other column ( the ,2 in the formula)
    =RANK(E2,IF(MOD(COLUMN($A2:$J2)-COLUMN($A2)+1,2)=0,$A2:$J2))

    Is it possible to get the rank of a value from a range of numbers from every nth column?

    Thank you.
    Mike

    https://exceljet.net/formula/max-of-every-nth-column#:~:text=To%20get%20the%20max%20of%20every%20nth%20column%2C,be%20entered%20with%20control%20%2B%20shift%20%2B%20enter.

  15. Hi there to all followers of this...

    With regards to the simple excel rank function.

    Anyone have an idea on how to start the order from a particular given number as opposed to starting from 1?

    i.e Manipulate the Order (optional) - a number that specifies how to rank values.

    The only options with rank eq appear to be:

    If 0 or omitted, the values are ranked in descending order, i.e. from largest to smallest.
    If 1 or any other non-zero value, the values are ranked in ascending order, i.e. from smallest to largest.

    Many Thanks,

    Bobby.

    1. Hello!
      For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. If you want to get the N largest values starting from 1, change the second parameter in the LARGE function. It's hard for me to guess from your description, but maybe this will help.

  16. PLS help me with this: I would like to rank only those from 80 and above with 90 as the highest or rank 1.

    80
    75
    83
    88
    79
    90
    thanks :-)

    1. i got it this way! i just combined rank() with IF():
      =if(A2>79,rank(A2,$A$2:$A$7),"")

      thanks anyway :-)

  17. Hi, my doubt is,how to get same rank holders names in a single cell?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  18. Hey, I want to rank by group but I also have duplicate values, whats the best formula without it duplicating/skipping the rank?
    Thanks!

    1. Hello!
      The article above describes how to rank with duplicates and without duplicates.
      Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

    2. duplicate and skipping the rank number I mean as below :)
      Rank Style WEEKEND SALES
      1 MZZ6495 556
      2 MZZ2170 210
      2 MZZ2282 210
      2 MZZ1560 210
      5 MZZ1539 203
      6 MZZ2443 178
      7 MZZ1982 151

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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