Best Of

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!

@KPH beat me to it, but I agree 100% with their recommendation. To make a small addition, mostly because I already had it typed… this will also setup the prefix mentioned in your comment:

=IF([Risk Priority Number]@row <= 49, "Non Relevant " + [Risk Priority Number]@row , IF([Risk Priority Number]@row <= 89, "Minor " + [Risk Priority Number]@row , IF([Risk Priority Number]@row <= 125, "Major " + [Risk Priority Number]@row , "Crit " + [Risk Priority Number]@row )))

Good catch @Carson Penticuff

If this was an exam, my teacher would be very unimpressed with me missing part of the question.

May I redeem myself by offering a slightly shorter way to add the number to the prefix (which I find easier to edit*)?

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

*For example adding a hyphen to separate the suffix, or adding another part to the string, can be done in one place now.