Skip to main content
Solved

Nested 3 IF() statements with 3 AND()

  • February 22, 2020
  • 3 replies
  • 29 views
J
Forum|alt.badge.img+2

What is wrong with my nested if? It says that that It’s invalid

IF(
  AND(
    {5dayswithnochange},
    {issueStatus🟢or🔴or🟠}=🟢
  ), 
  "Stable ⚖", 
  IF(
    AND(
      {5dayswithnochange}, 
      {issueStatus🟢or🔴or🟠}=🟠
    ), 
    "Stuck 🆘", 
    IF(
      AND(
        {5dayswithnochange},
        {issueStatus🟢or🔴or🟠}=🔴
      ), 
      "Broken 🔥", 
      "Too soon to tell ❔"
    )
  )
)

Best answer by Julien_Reszka

nevermind, I forgot to add the quotes “” around the emojis :green_circle: in the equality evaluations
Correct code

IF(
  AND(
    {5dayswithnochange},
    {issueStatus🟢or🔴or🟠}="🟢"
  ), 
  "Stable ⚖", 
  IF(
    AND(
      {5dayswithnochange}, 
      {issueStatus🟢or🔴or🟠}="🟠"
    ), 
    "Stuck 🆘", 
    IF(
      AND(
        {5dayswithnochange},
        {issueStatus🟢or🔴or🟠}="🔴"
      ), 
      "Broken 🔥", 
      "Too soon to tell ❔"
    )
  )
)

    3 replies

    J
    Forum|alt.badge.img+2
    • Julien_Reszka
    • Author
    • New Participant
    • Answer
    • February 22, 2020

    nevermind, I forgot to add the quotes “” around the emojis :green_circle: in the equality evaluations
    Correct code

    IF(
  AND(
    {5dayswithnochange},
    {issueStatus🟢or🔴or🟠}="🟢"
  ), 
  "Stable ⚖", 
  IF(
    AND(
      {5dayswithnochange}, 
      {issueStatus🟢or🔴or🟠}="🟠"
    ), 
    "Stuck 🆘", 
    IF(
      AND(
        {5dayswithnochange},
        {issueStatus🟢or🔴or🟠}="🔴"
      ), 
      "Broken 🔥", 
      "Too soon to tell ❔"
    )
  )
)
    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • February 23, 2020

    Glad you were able to fix your issue yourself!

    However, I happened to see your formula and thought you might be interested in a slightly simpler version. This version has less nesting and eliminates the need for the AND functions. It also uses the SWITCH function, which is really useful for when dealing with multiple possible values for a single field.

    IF ({5dayswithnochange},
    SWITCH({issueStatus🟢or🔴or🟠},
      "🟢", "Stable ⚖", 
      "🟠", "Stuck 🆘", 
      "🔴", "Broken 🔥"
    ),
    "Too soon to tell ❔"
)
    J
    J
    Forum|alt.badge.img+2
    • Julien_Reszka
    • Author
    • New Participant
    • February 23, 2020

    Glad you were able to fix your issue yourself!

    However, I happened to see your formula and thought you might be interested in a slightly simpler version. This version has less nesting and eliminates the need for the AND functions. It also uses the SWITCH function, which is really useful for when dealing with multiple possible values for a single field.

    IF ({5dayswithnochange},
    SWITCH({issueStatus🟢or🔴or🟠},
      "🟢", "Stable ⚖", 
      "🟠", "Stuck 🆘", 
      "🔴", "Broken 🔥"
    ),
    "Too soon to tell ❔"
)

    Tank you! simpler syntax indeed.

    Terms & ConditionsAccessibility statement