Does Smartsheets have a Google PERCENTRANK function equivalent?
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])
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!