Rank formula?

Will Chaspuri

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

J. Craig Williams

Will,

 

No.

Is there a limit to the rankings you need?

Can the data be sorted?

 

Craig

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

Ali Wigington

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

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

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. 

erober01

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

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.