Mountainviews
Level 1 (9 points)
iWork

Q: rank function missing some ranking numbers

I am using the ranking function and have noticed that in cases where I have 2 or more numbers with the same value, they are ranked the same but the next rank is missing. See the photo for what I mean. The rank #4 and #6 are missing. Is there a way to make sure that ranking numbers are not skipped?

Screen Shot 2016-08-22 at 11.18.28 PM.png

iMac 24", Mac OS X (10.6.3), Macbook Pro, Epson Pro 3880, NIK, Photoshop, Nikon D300, Spyder3

Posted on Aug 22, 2016 10:23 PM

Close
Mountainviews

Q: rank function missing some ranking numbers

  • All replies
  • Helpful answers

  • by SGIII,

    SGIII SGIII Aug 23, 2016 5:29 AM in response to Mountainviews
    Level 6 (10,796 points)
    Mac OS X
    Aug 23, 2016 5:29 AM in response to Mountainviews

    Are you trying to break ties? If so one way is to add an index column and base your RANK function on that, e.g.

     

    =B+ROW()/9999999

     

    This will add a very small number based on the row the value is in, thus making your second 82 on row 8 slightly larger than 82 on row 7.

     

    Then base your RANK function on the new index column.

     

    SG

  • by Mountainviews,

    Mountainviews Mountainviews Aug 23, 2016 6:08 AM in response to SGIII
    Level 1 (9 points)
    iWork
    Aug 23, 2016 6:08 AM in response to SGIII

    No, ties are fine to have. I need a way to allow ties and still carry on with the sequence without skipping numbers.