Comments on: Excel RANK functions with formula examples

Comments page 2. Total comments: 78

1. 

   Olive says:
   2019-10-29 at 12:44 pm

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

   1. 

      Svetlana Cheusheva says:
      2019-10-30 at 8:05 am

      Hi Olive,

      Check out these solutions: How to rank numbers uniquely

2. 

   bob denis says:
   2019-05-25 at 7:21 pm

   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. 

      Denis Bob says:
      2019-10-17 at 8:51 pm

      yes, you can do this through code in excel

3. 

   Matthew Collen says:
   2019-04-04 at 12:44 pm

   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. 

      Matthew Collen says:
      2019-04-05 at 6:13 pm

      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. 

         Rachel Grant Kiley says:
         2019-10-03 at 5:07 am

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

4. 

   Susan says:
   2018-11-24 at 10:21 pm

   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. 

      Svetlana Cheusheva says:
      2018-11-25 at 9:19 am

      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.