Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
IF / THEN FORMULA HELP  Multiple Numerals
How do I create a if / then formula to show one response for multiple numeral answers such as
IF Column1 is 1, 2 OR 3 then Column 2 is "acceptable"
4, 5, OR 7 then "tolerable"
6, 8, OR 9 then "undesirable"
10, 11 OR 12 then "intolerable
Comments

The two building block concepts you need are the OR() function and the nested IF() pattern.
This is your condition for acceptable:
OR(Column1 = 1, Column1=2, Column1=3)
You could also use AND in this case
AND(Column1 >= 1, Column1 <= 3)
Similarly for the other cases, though because your "tolerable" and "undesirable" mysteriously swap 6 and 7, the OR pattern might be better.
For future formula building, it is useful to note that you can nest these to make more complex rules. You might use this formula for "polarized"
OR(AND(Column1 >= 1, Column1 <= 3), AND(Column1 >= 10, Column1 <=12))
Now you want to make a set of IFs that give you what you want. You can think as each IF as a filter, and if it doesn't give you what you want, you put another IF in the false side
IF(<acceptable formula above>, "acceptable", <something to decide amongst the others>)
the <something to decide...> is actually another IF
IF(<tolerable>, <something to further decide>)
So in the end the IF pattern looks like:
=IF(<acceptable condition>, "acceptable", IF(<tolerable condition>, "tolerable", IF(<undesirable condition>, "undesirable", IF(<intolerable condition>, "intolerable", "error))))
Sometimes it's easier to create columns for each of the <xyz conditions>, build your nested IF statement referring to those columns, and then copy and paste the formulas from those condition columns to replace the column references to make your build formula. Then you can delete the temporary columns.

Thanks so much Adam! This is VERY helpful!

Based upon Adam's reply I created the following formula.
=IF(OR([Column1]1 = "1", [Column1]1 = "2", [Column1]1 = "3"), "acceptable", IF(OR([Column1]1 = "4", [Column1]1 = "5", [Column1]1 = "7"), "tolerable", IF(OR([Column1]1 = "6", [Column1]1 = "8", [Column1]1 = "9"), "undesirable", IF(OR([Column1]1 = "10", [Column1]1 = "11", [Column1]1 = "12"), "intolerable", "Undefined"))))
Neal
NealPatil @ gmx.com

Thanks Neal!!
For future knowledge...
Is there not a way to state IF Column1 = # through # (without any missing from the sequence) without individually listing each one? I know I can do less than / greater than but wasnt sure if that was my only option

Hi Laura,
If all of your values are greater than zero (and not blank), it will be easy. In your nested IF statement, you can use the "less than" symbol (<) in your IF statement, such as:
IF([Column1]1<4,"acceptable",IF([Column1]1<7,"tolerable",...
If the first one matches, it will not check further so you know that the value is 1, 2 or 3 because those are all less than four. If the next IF matches, you know it's NOT 1, 2 or 3 (because it had already matched and been marked "acceptable") and it's also less than 7 so must be 4, 5 or 6. And so on.
Hope this helps...
Jim
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives