Nested IF with AND and OR

maireann

Is there a good "Nested If" explainer out there? I've gone through many sample formulas and I have written simple formulas for each line of what I need to do, but I still find myself stumbling when I try to next them together. It seems there should be a formula tutorial out there that would walk you through the next phases, from the simple examples that are in the Template up through layering in more nuanced criteria (ORs and ANDs). Is there anything like that out there?

ISSUE 1: Need to set a value when multiple values are true.

For example, I want to set the RYG to "Red" when Type@row = "Issue" OR "Risk" I tried the following, which works fine, but is this really the best way to do it, and how do I tack on the rest of the criteria (see issue 2)?

=IF(OR(CONTAINS("Risk", Type@row), CONTAINS("Issue", Type@row)), "Red", "Green")

ISSUE 2: Multiple criteria

I'm working with 3 columns (Type, Impact, Likelihood) and need the following outcomes:

1. If (Type = Risk OR Issue) AND (Impact = "High"), set Red.
2. If (Type = Risk OR Issue) AND (Impact = "Medium"), set Yellow.
3. If (Type = Watch) AND (Impact = "High") AND (Likelihood = "Likely" OR "Very Likely"), set Yellow.
4. Otherwise, set Green.

Mike TV

@maireann

For Issue 1, you can shorten down the formula a little bit if the Type column will only have Risk, or Issue in the cell and not additional text such as "High Risk". You can shorten it like so:

=IF(OR(Type@row="Risk", Type@row="Issue"), "Red", "Green")

For Issue 2:

Status column formula:

=IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "High"), "Red", IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "Medium"), "Yellow", IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Very Likely"), Type@row = "Watch", Impact@row = "High"), "Yellow", "Green")))

The way I write my nested IF AND OR formulas is to map out the various options that I want a formula written for such as I've done above. Then I write it one piece at a time making sure each row is satisfied before going on to the next nested IF. For example:

=IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "High"), "Red"

This takes us through a solution for rows 1 and 2 and only tells the formula what to do when the AND OR is true. Once that is working after "Red" you add a comma and you're on the false section. The false section is where the next IF will begin. Once you begin that IF, you're back to telling the formula what to do when your results are true. When I got to the 3rd nested IF for rows 5 and 6 above, it's best to start out with the OR section before adding the AND conditions.

Mike TV

@maireann

For Issue 1, you can shorten down the formula a little bit if the Type column will only have Risk, or Issue in the cell and not additional text such as "High Risk". You can shorten it like so:

=IF(OR(Type@row="Risk", Type@row="Issue"), "Red", "Green")

For Issue 2:

Status column formula:

=IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "High"), "Red", IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "Medium"), "Yellow", IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Very Likely"), Type@row = "Watch", Impact@row = "High"), "Yellow", "Green")))

The way I write my nested IF AND OR formulas is to map out the various options that I want a formula written for such as I've done above. Then I write it one piece at a time making sure each row is satisfied before going on to the next nested IF. For example:

=IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "High"), "Red"

This takes us through a solution for rows 1 and 2 and only tells the formula what to do when the AND OR is true. Once that is working after "Red" you add a comma and you're on the false section. The false section is where the next IF will begin. Once you begin that IF, you're back to telling the formula what to do when your results are true. When I got to the 3rd nested IF for rows 5 and 6 above, it's best to start out with the OR section before adding the AND conditions.

Sameer Karkhanis

Try this,

=IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "High"), "Red",
    IF(AND(OR(Type@row = "Risk", Type@row = "Issue"), Impact@row = "Medium"), "Yellow",
        IF(AND(Type@row = "Watch", Impact@row = "High", OR(Likelihood@row = "Likely", Likelihood@row = "Very Likely")), "Yellow", "Green"
        )
    )
)

maireann

Thank you @Mike TV and @Sameer Karkhanis - both formulas worked perfectly! Now I will see if I can extend it just a little more to take into account whether the item is open/closed/mitigated, etc... :D