# Develop formula to break up project ranking figures into 4 quadrants

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.

~Amy

• 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.

• 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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!