Auto populate score based on 6 different criteria zones.
Hello,
I have been asked to build a scorecard that would automatically insert a numerical score (05) based on the input given.
So for example, if a perfect score of 100 = 5; 90  99 = 4; 80  89 = 3; 70  79 = 2; 60  69 = 1; and 0  59 = 0, I would like to develop a formula that would automatically insert the correct score for the value given.
In the past I have used a formula to auto populate a score, but it was only based on a low threshold and a high threshold. The formula looked like this:
=IF(ISBLANK(Result1), "", IF(Result1 > [Threshold Low]1, 0, IF(Result1 < [Threshold High]1, 5, 3)))
Resulting in a score of 0 if the entered value is below the Low Threshold Low, 3 if it's between the Threshold Low and Threshold High, and 5 if it is above the Threshold High.
I'm not sure how to write a formula that will encompass five thresholds. Has anyone encountered something like this before? Any help would be greatly appreciated.
Thanks!
Comments

Hi,
Try something like this.
=IF(ISBLANK(Result@row); ""; IF(Result@row = 100; 5; IF(AND(Result@row >= 90; Result@row <= 99); 4; IF(AND(Result@row >= 80; Result@row <= 89); 3; IF(AND(Result@row >= 70; Result@row <= 79); 2; IF(AND(Result@row >= 60; Result@row <= 69); 1; IF(AND(Result@row >= 0; Result@row <= 59); 0)))))))
The same version but with the below changes for your and others convenience.
=IF(ISBLANK(Result@row), "", IF(Result@row = 100, 5, IF(AND(Result@row >= 90, Result@row <= 99), 4, IF(AND(Result@row >= 80, Result@row <= 89), 3, IF(AND(Result@row >= 70, Result@row <= 79), 2, IF(AND(Result@row >= 60, Result@row <= 69), 1, IF(AND(Result@row >= 0, Result@row <= 59), 0)))))))
Depending on your country you’ll need to exchange the comma to a period and the semicolon to a comma.
Did it work?
Have a fantastic week!
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.

Thank you so much for the help!
The formula worked once I removed the = after the < or >. I have copied a version that worked for me below:
=IF(ISBLANK(Result@row), "", IF(Result@row = 100, 5, IF(AND(Result7 > 90, Result7 < 99), 4, IF(AND(Result@row > 80, Result@row < 89), 3, IF(AND(Result@row > 70, Result@row < 79), 2, IF(AND(Result@row > 60, Result@row < 69), 1, IF(AND(Result@row > 0, Result@row < 59), 0)))))))
However, each item in my scorecard has different scoring criteria. Rather than manually editing the formula for every entry, I would rather have the formula pull from Threshold columns. I experimented with modifying the formula above to do so. It looks like this:
=IF(ISBLANK(Result@row), "", IF(Result@row = [Threshold 5]@row, 5, IF(AND(Result@row > [Threshold 5]@row, Result@row < [Threshold 4]@row), 4, IF(AND(Result@row > [Threshold 4]@row, Result@row < [Threshold 3]@row), 3, IF(AND(Result@row > [Threshold 5]@row, Result@row < [Threshold 2]@row), 2, IF(AND(Result@row > [Threshold 2]@row, Result@row < [Threshold 1]@row), 1, IF(AND(Result@row < [Threshold 1]@row), 0)))))))
For the above formula the threshold columns have been set up as follows:
Threshold 1: 59
Threshold 2: 69
Threshold 3: 79
Threshold 4: 89
Threshold 5: 99The formula works if it's below threshold 1 and above threshold 5 but for everything else it the cell remains blank (containing neither and error message nor a numerical score). I'm not sure where I went wrong. Do you have any suggestions for how I could fix this?
Thanks again! I am very grateful for the advice!

Try something like this...
=IF(ISBLANK(Result@row), "", IF(Result@row < [Threshold 1]@row, 0, IF(Result@row < [Threshold 2]@row, 1, IF(Result@row < [Threshold 3]@row, 2, IF(Result@row < [Threshold 4]@row, 3, IF(Result@row < [Threshold 5]@row, 4, 5))))))
.
If you look at your modified formula and compare it to the data in your Threshold columns, you will see that you have your less than and greater than signs mixed up. For example...
IF(AND(Result@row > [Threshold 5]@row, Result@row < [Threshold 4]@row), 4
You are saying:
If Result@row is GREATER THAN 99 AND LESS THAN 89.......
That isn't possible. Since that statement is not true, it will not return a value which is why you are getting a blank.

That worked!! Thank you so much for all of your help and your excellent explanations. I have learned so much from this!

Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 465 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!