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

Find more posts tagged with

Formulas

Services

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…