Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Rank formula?

Will Chaspuri
Will Chaspuri ✭
edited 12/09/19 in Archived 2017 Posts

Is there any equivalent for rank formula on Excel?

 

I would like to create a ranking column based on value  of another column

 

example :

Score | Rank

20      |  2

30      |  1

2        |  5

12       | 3

5        | 4

 

Thanks

Tags:
· Share on FacebookShare on Twitter

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Will,

     

    No.

    Is there a limit to the rankings you need?

    Can the data be sorted?

     

    Craig

    · Share on FacebookShare on Twitter
  • Xavier LN
    Xavier LN ✭

    It would be really helpful to have this RANK formula available.

    This is an essential feature when using Smartsheet to create Roadmap, which is one of the commercial arguments of Smartsheet. Ranking is a must-have feature to prioritize items in a roadmap.

    Is there any plan to add this soon?

    Thank you,

    Regards,

    Xavier

    · Share on FacebookShare on Twitter
  • Ali Wigington
    Ali Wigington ✭

    I would also like to put a vote in for this formula! 

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Have you tried using the new SMALL, LARGE, and COLLECT functions released August 19th, 2017?

    You might not be able to answer questions like "what rank am I?" (easily) but you can answer questions like "what are the top 10?" and then use LOOKUP to determine the answer to the first question.

    Craig

    · Share on FacebookShare on Twitter
  • Erin.Kozma
    Erin.Kozma ✭

    I would also love to see this functionality! We have an inner office competition going on as a part of a project. I would love to have conditional formatting to highlight our 1st, 2nd and 3rd place people. 

    · Share on FacebookShare on Twitter
  • erober01
    erober01 ✭
    edited 04/24/19

    Hi everyone!

    If you're still needing help on this, but I found a workaround - it's not very pretty, but if you hide columns or pull columns into a report/ dashboard it won't matter.  You'll need 3 columns for this, and will create 2 more in the process.  1) "Unique Identifier" (for me, this is a name so the column is "Name", a unique Identifier for each score (such as a name, a number, whatever), in a column next to 2) "Score", the score.  Next to the score, is "Rank", a column you can number 1-x (whatever rankings you want). 

    Next, next to the rank column, use a LARGE formula: Large([score column], [The cell containing the rank you are looking for]).  This should order the scores you're looking for 1-x next to the rank.  As those numbers change, the rank will update accordingly.  For my chart, I am using scores for each name, so I insert a column between the rank and the ranked score and do an index formula to pull the name next to its score.

    I've never tried to explain my workarounds before, so if this is confusing please send me what you're trying to do and I can create it for you or try to explain better!

    · Share on FacebookShare on Twitter
  • Kristen Guy
    Kristen Guy ✭✭
    edited 05/17/19

    Thanks for the detailed comment!  I ended up needing to do something like this today, and the tip about the LARGE formula and extra columns was very useful. I've included my solution below in case it proves helpful to others. 

     

    I started with a Main Sheet where the scoring actually takes place, and opened a separate Rankings sheet, and set up the following columns:

     - Rank:  no formula, just a number, going 1-15 on the first 15 rows

     - Score: Large({Main Sheet - score}, Rank@row)

     - ID: index({Main Sheet}, match(Score@row, {Main Sheet - score},0),match("ID",{Main Sheet - column names},0))

    I then added in other columns I needed on this Rankings sheet for context/details, and used a report to display the items I wanted on a dashboard.

     

    One limitation-- the above strategy will start to have issues if I end up with two items that have the exact same score. That's pretty unlikely on our end given how the scoring system works, but for those dealing with smaller scales where repeats are more likely, this might not be the right solution.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories