Feature Request: Rank order macro

Haig Assadourian

We recently migrated from a google doc to smartsheet for tracking a project portfolio.  Google docs offered the ability to apply a Rank Order macro to let us sort the list based on a calcuated priority score for each project in the list.  Here is an example of the macro:

 

=rank(AQ3,AQ:AQ)

 

Smartsheets lets us score projects for priority but we would like to be able to use a Rank macro so we can refer to the ranked numerical order of the project (as opposed to just the total score of the projects).

 

In the example above, the macro compares the score in cell AQ3 with all other numerical scores in column AQ and determines its rank.

Jeremy Michels

You can do this. I have this in 50 sheets now. It is a formula problem. 

Travis

Jeremy - We are interested in seeing how you are able to do this! Would you mind explaining your process and formula? 

Haig Assadourian

Please do share if you can Jeremy.  I've tried applying various versions of that macro with no success.  What is the macro you've used?

Jeremy Michels

I want to make sure I am on the same page. What we have done is created a rank or a score column for child rows. That totals to a parent row and gives you a total score. Then the total score is measured in a seperate column to determine wich is the most critical to work on. Ours works both in positive or negative direction. The hieghtest score is the most important or the least. Is this what you are looking to achieve? 

Haig Assadourian

Jeremy,

If you look at macro in my initial post, it looks at all the calculated project scores on a single sheet and then determines a descending order rank from 1 to X.  That way, you can sort upon that column from A to Z and it will rank all projects on the sheet in that descending rank order.  

 

I think we are both scoring projects similarly with the higher score representing a higher priority.  But I'm interested in using a simple macro I've used in google and excel to translate those scores into a ranked ordering scheme.   In other words, if the highest project scores were 60, 59, 59, 58, 52....Those five projects would be "ranked" 1,2,2,4,5 by that macro.  Note that the rank macro assign the same rank order for projects that have the same priority scoring.

 

If anyone has insights on how to do this in Smartsheets please speak up.

 

Thanks!

Jeremy Michels

I do the same thing in scoring but use the sort fearture on the column to put similar scores in a set order. So the most important are at the top. We have a column that has a priority rating to it. We sort that column so in you reference we have a 60 core it shows as number one and then is set to the top of the page. As items change value we resort the page and get the list to be corrected. In some of the sheets we do this with a symbol. The symbol with represent a set value and when we sort we can keep them in the correct order.