Develop formula to break up project ranking figures into 4 quadrants

Amy Scherer

Hello! I have developed a format for my team to utilize a ranking process for incoming continuous improvement ideas. The methodology is called RICE Ranking which you may or may not have heard of. Within the ranking, we want the info to automatically designate which quadrant the idea falls into based on it's rank which should dictate where it goes based on the logic: Below Threshold, Low Priority, Medium Priority or High Priority.

I'm pasting in a screen shot of my sheet which explains the way each column of data is setup to work; i.e. formula, dropdowns, text/number, etc. I'm also going to put in a formula writeup that explains essentially what I'd like the sheet to do. My preference would be to not have to go in and change the figures each time. I'd prefer it do it's own math, but I'm not sure that's possible. Anyway...thank you SO much for any insight! I spoke to someone at Smartsheet yesterday and he suggested I post this with the formula logic we discussed. The challenge is actually making that logic work.

SCREENSHOT:

• Anything with a gold star contains a formula.
• The RICE Score formula = Reach*Impact*Confidence/Effort
• The ranking is a formula that ranks based on all of the RICE Score figures.
• The "Quadrant" column all the way to the right is where I need this formula I'm struggling with to go. Formula logic below:

FORMULA LOGIC:

If the row's ranking is between 1 and 25 and there are 100 rows with ranks, "Below Threshold", If the ranking is between 1 and 12 and there are at least 50 rows with ranks, "Below Threshold", If the ranking is between 1 and 6 and there are at least 25 rows with ranks, "Below Threshold", If the ranking is between 26 and 50 and there are at least 100 rows with ranks, "Low priority", If the ranking is between 12 and 25 and there are at least 50 rows with ranks, "Low priority", If the ranking is between 7 and 12 and there are at least 25 rows with ranks, "Low priority", If the ranking is between 51 and 75 and there are at least 100 rows with ranks, "Medium priority", If the ranking is between 26 and 37 and there are at least 50 rows with ranks, "Medium priority", If the ranking is between 12 and 18 and there are at least 25 rows with ranks, "Medium priority" , If the ranking is between 76 and 100 and there are at least 100 rows with ranks, "High priority", If the ranking is between 38 and a50nd there are at least 50 rows with ranks, "High priority", If the ranking is between 18 and 25 and there are at least 25 rows with ranks, "High priority"

• This is the logic for how I would like the data to work. Again, my preference would be not to have to change the #s within the formula every time we work on this. As an example - if we start it out at 25 total, and break that into quadrants, that's great until the following week when we add in another 3 one day, and someone else adds in another 4 another day. That would require constant editing.

Thank you so much for any help you can offer!

~Amy

Paul Newcome

So correct me if I am wrong, but it looks like you are breaking this into quarters. What if we just took the count of rows and said that if the rank is in the bottom 25%, "Below Threshold", if in 26%-50% range, "Low Priority", so on and so forth. Would that work? Then it would automatically account for how many rows are in the sheet and you don't have to write separate arguments for 25 rows and 50 rows and 100 rows, and so on and so on...?

Let me know if you think that would work, and I will see if we can come up with something for you.

Leibel S

@Amy Scherer

You can divide the rank by the total count of ideas, this would give you the percentile it is in...

Example below:

If(ranking/count(ranking:ranking)<.25,"Below Threshold")

Paul Newcome

@Leibel S pretty much has the idea started.

=IF(Ranking@row/COUNT(Ranking:Ranking) < .25, "Below Threshold", IF(Ranking@row/COUNT(Ranking:Ranking) < .5, "Low Priority", IF(Ranking@row/COUNT(Ranking:Ranking) < .75, "Medium Priority", "High Priority")))

Paul Newcome

So correct me if I am wrong, but it looks like you are breaking this into quarters. What if we just took the count of rows and said that if the rank is in the bottom 25%, "Below Threshold", if in 26%-50% range, "Low Priority", so on and so forth. Would that work? Then it would automatically account for how many rows are in the sheet and you don't have to write separate arguments for 25 rows and 50 rows and 100 rows, and so on and so on...?

Let me know if you think that would work, and I will see if we can come up with something for you.

Amy Scherer

Paul -- YES! That's my hope. Do you think that could work? It can also be based off of the score vs. the rank if that's better.

Leibel S

@Amy Scherer

You can divide the rank by the total count of ideas, this would give you the percentile it is in...

Example below:

If(ranking/count(ranking:ranking)<.25,"Below Threshold")

Paul Newcome

@Leibel S pretty much has the idea started.

=IF(Ranking@row/COUNT(Ranking:Ranking) < .25, "Below Threshold", IF(Ranking@row/COUNT(Ranking:Ranking) < .5, "Low Priority", IF(Ranking@row/COUNT(Ranking:Ranking) < .75, "Medium Priority", "High Priority")))

Amy Scherer

OK that did it! You guys are so awesome! I did switch the order around as we are counting 1 as the highest rank, but it works! Thank you so much.

This is my first successful experience out of 3 with Communic

Paul Newcome

Happy to help. 👍️