IF / THEN FORMULA HELP - Multiple Numerals

Laura

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

 

 

Adam Overton

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.

 

 

 

 

 

 

 

 

Laura

Thanks so much Adam! This is VERY helpful!

 

NealPatil10292

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

Laura

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  

 

CanadaJim

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