Does Smartsheets have a Google PERCENTRANK function equivalent?

james.quast46986

Hello, does anyone have a solution for the excel / google function “PERCENTRANK()” or the “RANK” function.  We want to consolidate our roadmap planning into Smartsheets and use one of these functions to automatically score from 1 to 10 each item in the list.  The below image shows how this is done today in Google.  The user can change the revenue and it is re-ranked based on how it is within the population on each change to the revenue amount.

PERCENTRANK(data,value, [significant_digits])

Andrée Starå

Hi James,

Unfortunately, there isn't a PERCENTRANK function at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

Here are some potential workarounds:

https://community.smartsheet.com/discussion/using-collect-function-within-large-function

https://community.smartsheet.com/discussion/rank-formula

https://community.smartsheet.com/discussion/feature-request-rank-order-macro

Would that work/help?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

james.quast46986

Hello, I see there are two new formulas added to the list RANKEQ and RANKAVG.  These two seem to produce the same results.  Is there any examples or use cases so I can see on what these two are for?

Andrée Starå

Hi James,

I haven't had time to test those out yet, and I have found no examples except the one in the Formula Example Sheet. https://app.smartsheet.com/b/home?sc=mjjH3-k10qQ,mwFH3yU133o

Hope that helps!

Eric M Oliveira

Hi James, 

 

Andree is correct the below link is a great resource to play around with the RANKAVG and RANKEQ functions: 

 

https://app.smartsheet.com/sheets/XpQwV93RMM7wH7vHCPrVHj8wR9vXMVQWRmv9G7C1?view=grid

 

Based on your description provided of ranking the desired values from 1-10 I do believe the RANKEQ would be the best option. This function provides a whole value when ranking numbers meaning if you have 3 numbers in a column "25, 25, 30" and you wanted to know the ranking of the value 25 it would produce the value 2. It produces the value 2 because 25 is the second-highest number. This is further outlined here: https://help.smartsheet.com/function/rankeq

 

The formula for the above would look like this:

=RANKEQ(25,[Number Column]:[Number Column])

 

RANKAVG would produce the value 2.5 because they're two values set as 25 and it is averaging the difference of the two. This is further outlined here: https://help.smartsheet.com/function/rankavg

 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support