If statement with multiple conditions

Question

Formula goes up to "3- Checklist Complete" but stops there and I cannot figure it out.&nbsp;&nbsp;
If([Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation",If([Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= false, "2 - Checklist Incomplete",If([Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",If([Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals",If([Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete"------------------------------Sarah Burres------------------------------

markshnier__you · Answer

Unlike Excel you generally don't need to nest your IFs. You just list the conditions and the formula will keep checking them in sequence and use the reduk5 for the first condition which is true.&nbsp;

Formula goes up to "3- Checklist Complete" but stops there and I cannot figure it out.&nbsp;&nbsp;

Try this&nbsp;

IF(
[Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation",[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= false, "2 - Checklist Incomplete",[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals",[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete")

------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

sarahburres · Answer

Didn't work.&nbsp;  &nbsp; Here is the entire formula.&nbsp; The other Levels work but not Level 4. &nbsp; If([Level]="Level 1" and [Documents Available]=false, "1 - Waiting on Documentation", [Level]="Level 1" and [Documents Available]=true and [Approvals Needed]&lt;&gt;"", "2 - Waiting on Approvals", [Level]="Level 1" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete", &nbsp; If([Level]="Level 2" and [Documents Available]=false, "1 - Waiting on Documentation", [Level]="Level 2" and [Documents Available]=true and [Approvals Needed]&lt;&gt;"", "2 - Waiting on Approvals", [Level]="Level 2" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete", &nbsp; If([Level]="Level 3" and [Documents Available]=false, "1 - Waiting on Documentation", [Level]="Level 3" and [Documents Available]=true and [Approvals Needed]&lt;&gt;"", "2 - Waiting on Approvals", [Level]="Level 3" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete", &nbsp; If( [Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation", [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= false, "2 - Checklist Incomplete", [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete", [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals", [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete") &nbsp; &nbsp; )))

chayceduncan · Answer

I would suggest updating your formula to read like this - to Marks point you don't have to nest if statements and I find this is easier to read:&nbsp;

If(
&nbsp; &nbsp; [Level]="Level 1" and [Documents Available]=false, "1 - Waiting on Documentation",
&nbsp; &nbsp; [Level]="Level 1" and [Documents Available]=true and [Approvals Needed]&lt;&gt;"", "2 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 1" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
&nbsp; &nbsp; [Level]="Level 2" and [Documents Available]=false, "1 - Waiting on Documentation",
&nbsp; &nbsp; [Level]="Level 2" and [Documents Available]=true and [Approvals Needed]&lt;&gt;"", "2 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 2" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
&nbsp; &nbsp; [Level]="Level 3" and [Documents Available]=false, "1 - Waiting on Documentation",
&nbsp; &nbsp; [Level]="Level 3" and [Documents Available]=true and [Approvals Needed]&lt;&gt;"", "2 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 3" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= false, "2 - Checklist Incomplete",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","
"")

The actual issue with your formula though is that you need to reorder you last two statements

&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","

Quickbase will stop reading the statement once it finds an answer it likes. So for stage 3 and beyond:

&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","

All 3 are checking if Checklist complete = true. But once QB sees that then it never has to go past stage 3 since both conditions pass. Leve is 4, documents available is true and checklist is complete, stop rendering so it will never see that 4 and 5 exist. You should reorder as:

&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]&lt;&gt;"", "4 - Waiting on Approvals",
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","
&nbsp; &nbsp; [Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
&nbsp;

------------------------------Chayce Duncan------------------------------

sarahburres · Answer

THis worked!&nbsp; Thank you!------------------------------Sarah Burres------------------------------

Forum Discussion

If statement with multiple conditions