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:
- If (Type = Risk OR Issue) AND (Impact = "High"), set Red.
- If (Type = Risk OR Issue) AND (Impact = "Medium"), set Yellow.
- If (Type = Watch) AND (Impact = "High") AND (Likelihood = "Likely" OR "Very Likely"), set Yellow.
- Otherwise, set Green.