Multiple IF statements with > / < constraints.

JoHaan

Hello all,

I am trying to make cell A display the number of cell B and then put a prefix on it depending on the value of the number. Below is my criteria.

<49 "not relevant"

50-89 "Minor"

90-124 "Major"

>125 "Critical"

To test this, I did this formula, just to see if I could get the formula working.

=IF([Risk Priority Number]@row < 48, "Non Relevant", IF([Risk Priority Number]@row > 49 and < 89, "Minor", IF([Risk Priority Number]@row > 90 and < 124, "Major", "Crit")))

But it is giving me the #unparseable error.

Any assistance would be AMAZING! Thank you!!!

KPH

Hi @JoHaan

To create a function using IF and AND, you need to put the AND function at the start and then the two pieces of logic, instead of writing "and" within the logic. Like this:

=IF([Risk Priority Number]@row < 48, "Non Relevant",IF(AND([Risk Priority Number]@row >= 49, [Risk Priority Number]@row <= 89), "Minor"))

Not

=IF([Risk Priority Number]@row < 49, "Non Relevant",IF([Risk Priority Number]@row > 49 and < 89, "Minor"))

However, because the IF functions are nested the second one is only evaluated when the first is false, so you do not need the AND.

This is much simpler:

=IF([Risk Priority Number]@row < 48, "Non Relevant", IF([Risk Priority Number]@row <= 89, "Minor", IF([Risk Priority Number]@row <= 124, "Major", "Critical")))

It means:

If Risk Priority Number <49 is true then return "Not Relevant", if false, evaluate the next IF

…IF Risk Priority Number less than or equal to 89 is true then return "Minor", if false, evaluate the next IF

…IF Risk Priority Number less than or equal to 124 is true then return "Major", if false, return "Critical"

Note - In your example, if the Risk Priority Number is 49 or 125, nothing would be returned. So I used less than or equal to , rather than less than.

Hope that gets you going. Enjoy!