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





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




    Is there a limit to the rankings you need?

    Can the data be sorted?



  • 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,



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

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


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

  • 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!

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

This discussion has been closed.