Nested If Statement Assistance

Platform discussions Special workflows & use cases
,
1

Hello Monday Community: I could use some help with this Multi condition formula. The board has a series of mirror columns - each of the columns that this formula looks at, is a mirror column.

  • First Condition:
  • Checks if the Pass/No Pass column is “Yes”.
  • Checks if either SG1 Lesson Check or SG2 Lesson Check is “1UFLI L1 A”.
  • Checks if any of the UFLI 1 a (1), UFLI 1 a (2), or UFLI 1 a (3) columns contain “0”, are blank, or contain “1”.
  • If all these conditions are met, it returns “Yes”.
  • Second Condition:
  • Checks if the Pass/No Pass column is “No” or “Absent”.
  • Checks if either SG1 Lesson Check or SG2 Lesson Check is “1UFLI L1 A” or is blank.
  • Checks if any of the UFLI 1 a (1), UFLI 1 a (2), or UFLI 1 a (3) columns are blank.
  • If all these conditions are met, it returns “No”.
  • Default Condition:
  • If none of the above conditions are met, it returns an empty string.

This is the formula:
IF(
AND(
{Pass/No Pass} = “Yes”,
OR({SG1 Lesson Check} = “1UFLI L1 A”, {SG2 Lesson Check} = “1UFLI L1 A”),
OR({UFLI 1 a (1)} = “0”, {UFLI 1 a (1)} = “”, {UFLI 1 a (2)} = “0”, {UFLI 1 a (2)} = “”, {UFLI 1 a (3)} = “0”, {UFLI 1 a (3)} = “”, {UFLI 1 a (1)} = “1”, {UFLI 1 a (2)} = “1”, {UFLI 1 a (3)} = “1”)
),
“Yes”,
IF(
AND(
OR({Pass/No Pass} = “No”, {Pass/No Pass} = “Absent”),
OR({SG1 Lesson Check} = “1UFLI L1 A”, {SG2 Lesson Check} = “1UFLI L1 A”, {SG1 Lesson Check} = “”, {SG2 Lesson Check} = “”),
OR({UFLI 1 a (1)} = “”, {UFLI 1 a (2)} = “”, {UFLI 1 a (3)} = “”)
),
“No”,
“”
)
)

It all works, except when the status column has a status of “no” or “absent”, it returns a blank value. I have isolated each of the conditions to test and individually they work, once they are together it does not. Any help would be ever so appreciated!
Christina Kelley

2

Hi Christina,

The best thing to do is isolate each condition.
Create a formula column with only:

IF(
AND(
OR({Pass/No Pass} = “No”, {Pass/No Pass} = “Absent”),
OR({SG1 Lesson Check} = “1UFLI L1 A”, {SG2 Lesson Check} = “1UFLI L1 A”, {SG1 Lesson Check} = “”, {SG2 Lesson Check} = “”),
OR({UFLI 1 a (1)} = “”, {UFLI 1 a (2)} = “”, {UFLI 1 a (3)} = “”)
),
“No”,
“”
)

and see if it works.

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.

3

Hi Gilles,
Thank you for your help. It turned out that Monday interpreted the " " around certain values to mean to look for text - I removed them and the formula worked like a charm!
Have a great week.
Christina