Multiple IF AND statements a cell
Looking to take a screening subscore that could range from 0 to 100 and assign another score to that based on ranges of the subscore.
If subscore is greater than 0 and <=20, return "0"
If subscore is greater than 20 and <=40, return "1"
If subscore is greater than 40 and <= 60, return "2"
etc...
How do I write the formula for that? I can make each work individually but cannot successfully tie them all together in a single formula.
Thanks
Best Answers

Hi @Mike Ferringer ,
Try this:
=IF(AND([subscore column name]@row>0, [subscore column name]@row<=20),0,IF([subscore column name]@row<=40,1,IF([subscore column name]@row<=60,2,IF([subscore column name]@row<=80,3,IF([subscore column name]@row<=100,4,"")))))
This translates to:
If the subscore is greater than 0 and less than or equal to 20, show 0. Otherwise, if it's less than or equal to 40, show 1. Otherwise, if it's less than or equal to 60, show 2. Otherwise, if it's less than or equal to 80, show 3. Otherwise, if it's less than or equal to 100, show 4. Otherwise (which really only leaves values less than or equal to zero, or more than 100), leave blank.
Hope this helps! Let me know if it works.
Best,
Heather

Worked perfectly Heather! Thank you for the quick reply!
Answers

Hi @Mike Ferringer ,
Try this:
=IF(AND([subscore column name]@row>0, [subscore column name]@row<=20),0,IF([subscore column name]@row<=40,1,IF([subscore column name]@row<=60,2,IF([subscore column name]@row<=80,3,IF([subscore column name]@row<=100,4,"")))))
This translates to:
If the subscore is greater than 0 and less than or equal to 20, show 0. Otherwise, if it's less than or equal to 40, show 1. Otherwise, if it's less than or equal to 60, show 2. Otherwise, if it's less than or equal to 80, show 3. Otherwise, if it's less than or equal to 100, show 4. Otherwise (which really only leaves values less than or equal to zero, or more than 100), leave blank.
Hope this helps! Let me know if it works.
Best,
Heather

Worked perfectly Heather! Thank you for the quick reply!

@Mike Ferringer Great! Glad it worked. Have a wonderful day.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 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
 302 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!