IF / THEN FORMULA HELP - Multiple Numerals

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.

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

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