How can we help?

New post
0

Ranking without skip values

Avatar
Charly Saß
None
Follow

Hello Community, I have a Problem and hope there is someone with an idea.

I have a hidden table with an employee ranking and a formula in a value pair component, which shows how much the viewing employee need to get an higher rank. I used the Rank formula in the table and in the value pair formula I use a select formula to get the values from the employee with the rank from the viewing employee -1 to get the higher one. My Problem is, that the rank formula skips values if there are 2 or more rows with the same rank.

For example it looks like 1, 2, 2, 4, 5.... but what I need is 1, 2, 2, 3, 4...

Is there anyone with an idea clearing the ranking formula or how to check in the value pair formula if the next rank didn't exists?

I've tried to use an If formula like "when the selected value is blank, then select values from rank -2 and so on, but it didn't work. I checked what happened if the higher rank didn't exists and get no results, so I can't use an IF formula to check if the value is blank or anything.

I also asked the support team, but since a week I got only formulas for the rank column, which didn't rank correct. Looks like this:

 

Date Votes

3 comments

  • Avatar
    Parker Selman Official comment

    Hi Charly, 

    Thanks for the post! 

    In this case, RANK alone cannot get you the output you're looking for, as it will always skip places when duplicates are found. However, we can instead rank a grouped list of values, and then use a LOOKUP to repeat the rank values for duplicates rather than ranking the entire list with duplicates included.

     

    This formula, for copying and pasting, is:

    LOOKUP(&'2923af62',GROUP(&'2923af62'),RANK(GROUP(&'2923af62'),"1"))

    This way, you rank the list of grouped values first, and then simply map that grouped value onto the records in the actual table. 

    I hope this helps! 

    Parker

  • 0
    Avatar
    Charly Saß

    Hello Parker,

     

    thank you for this  great solution.

     

    Could you please edit your post as far as possible and edit the screenshot because you're showing personal informations about our employees. We could get a lot of problems because of the german data privacy protection rights!

  • 0
    Avatar
    Parker Selman

    Hi Charly,

    Apologies - that's been removed. 

    I'm glad that solution works for you however!

    Parker

Please sign in to leave a comment.