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