Need help with "IF" formula

Vivien Chong

I am unable to get the formula right for the following scenario:

Column 1: Yes / No

Column 2: Yes / No

If Column 1 and/or Column 2 is blank, then "Not Completed"

If Column 1 and Column 2 is Yes, then "Completed"

If Column 1 is Yes, Column 2 is No, then "In Progress"

If Column 1 is No, Column 2 is Yes, then "KIV"

 

=IF(ISBLANK([Column 1]1), "Not Completed", IF(ISBLANK([Column 2]1), "Not Completed", IF([Column 1]1 = "Yes", IF([Column 2]1 = "Yes", "Completed", IF([Column 1]1 = "Yes", IF([Column 2]1 = "No", "In Progress", IF([Column 1]1 = "No", IF([Column 2]1 = "Yes", "KIV"))))))))

 

But the last part of the formula doesn't work (IF([Column 1]1 = "No", IF([Column 2]1 = "Yes", "KIV").  It just return blank cell.

 

Please help.  Thanks.

Frank Falco

Try this version:

=IF(OR(ISBLANK(Column1@row),ISBLANK(Column2@row)),"Not Complete",

IF(Column1@row="Yes",IF(Column2@row="Yes","Complete","In Progress"),

IF(Column2@row="No","KIV","---")))

Assuming there is not other options than Blank, Yes or No

1) Check if either are Blank = Not Complete

2) If not, then check if Col1 is Yes, then if Col2 is Yes = Complete, else must be No = In Progress

3) As Col1 is neither blank nor Yes so must be No, then check if Col2 is No = KIV

else = "---" (unknown state - should not happen)

Use @row instead of row numbers as makes it easier for SS to calculate the code

Vivien Chong

Thanks Frank.  Your formula works.  Appreciate your assistance.

I wonder why my formula couldn't work completely, though the logic is correct.