We help IT Professionals succeed at work.

Nested IF

ioecho
ioecho asked
on
Excel is not accepting this formula.

THIS IS MY CURRENT FORMULA
=IF(AND(T2="Not Started",U2="Not Started",V2="Not Started",W2="Not Started"),"Not Started",IF(AND(T2="InProgress",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Not Started"),"InProgress", IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Completed"), "Completed",IF(AND(T2="Completed",U2="InProgress",V2="InProgress",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="InProgress",W2="Not Started","InProgress",IF(AND(T2="Not Started",U2="Not Started",V2="Not Started",W2="Not Started"),"Not Started",IF(AND(T2="InProgress",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Not Started"),"InProgress", IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Completed"), "Completed",IF(AND(T2="Completed",U2="InProgress",V2="InProgress",W2="Not Started"),"InProgress",IF(AND(T2="Not in Scope",U2="Not in Scope",V2="Not in Scope",W2="Not in Scope"),"Not in Scope","Inappropriate Status Combination")))))))))))))))))

 It throws this message.

THIS IS WHAT EXCEL SAYS
Comment
Watch Question

Codrut TambuSr. Software Developer Engineer

Commented:
Use semicolon (;) instead of comma(,) like:
=IF(1<2;"TRUE";"FALSE")
=IF([logical_condition];[value_if_true];[value_if_false])
=IF(AND(T2="Not Started",U2="Not Started",V2="Not Started",W2="Not Started"),"Not Started",IF(AND(T2="InProgress",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Not Started"),"InProgress", IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Completed"), "Completed",IF(AND(T2="Completed",U2="InProgress",V2="InProgress",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="InProgress",W2="Not Started"),"InProgress",IF(AND(T2="Not Started",U2="Not Started",V2="Not Started",W2="Not Started"),"Not Started",IF(AND(T2="InProgress",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Not Started",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Not Started",W2="Not Started"),"InProgress",IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Not Started"),"InProgress", IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Completed"), "Completed",IF(AND(T2="Completed",U2="InProgress",V2="InProgress",W2="Not Started"),"InProgress",IF(AND(T2="Not in Scope",U2="Not in Scope",V2="Not in Scope",W2="Not in Scope"),"Not in Scope","Inappropriate Status Combination"))))))))))))))))

Author

Commented:
Still not working...

=IF(AND(T2="Not Started",U2="Not Started",V2="Not Started",W2="Not Started");"Not Started";IF(AND(T2="InProgress",U2="Not Started",V2="Not Started",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Not Started",V2="Not Started",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Completed",V2="Not Started",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Not Started");"InProgress"; IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Completed"); "Completed";IF(AND(T2="Completed",U2="InProgress",V2="InProgress",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Completed",V2="InProgress",W2="Not Started");"InProgress";IF(AND(T2="Not Started",U2="Not Started",V2="Not Started",W2="Not Started");"Not Started";IF(AND(T2="InProgress",U2="Not Started",V2="Not Started",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Not Started",V2="Not Started",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Completed",V2="Not Started",W2="Not Started");"InProgress";IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Not Started");"InProgress"; IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="Completed"); "Completed";IF(AND(T2="Completed",U2="InProgress",V2="InProgress",W2="Not Started");"InProgress";IF(AND(T2="Not in Scope",U2="Not in Scope",V2="Not in Scope",W2="Not in Scope");"Not in Scope";"Inappropriate Status Combination"))))))))))))))))

Author

Commented:
Perfect! Thank you so much!
Top Expert 2008

Commented:
Here is a shorter and, hopefully, depending on your perspective, more maintainable formula. It works by building a four digit key from the four source cells where each digit represents one cell as "Not Started" = 1, "InProgress" = 2, "Completed" = 3, and "Not in Scope" = 4. The key is then looked up in a table of possible valid keys and the corresponding result returned. If no key is found then "Inappropriate Status Combination" is returned.

=IFERROR(INDEX({"Not Started","In Progress","In Progress","In Progress","In Progress","Completed","In Progress","In Progress","Completed","Not in Scope"},MATCH(MATCH(LEFT(T2,5),{"Not S","InPro","Compl","Not i"},0)&MATCH(LEFT(U2,5),{"Not S","InPro","Compl","Not i"},0)&MATCH(LEFT(V2,5),{"Not S","InPro","Compl","Not i"},0)&MATCH(LEFT(W2,5),{"Not S","InPro","Compl","Not i"},0),{"1111","2111","3111","3311","3331","3333","3221","3321","3333","4444"},0)),"Inappropriate Status Combination")

Kevin

Author

Commented:
Same as my last question. I appended to the formula and again excel is not accepting. I need a work around.

=IF(AND(T172="Not Started",U172="Not Started",V172="Not Started",W172="Not Started"),"Not Started",IF(AND(T172="InProgress",U172="Not Started",V172="Not Started",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Not Started",V172="Not Started",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Completed",V172="Not Started",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Completed",V172="Completed",W172="Not Started"),"InProgress", IF(AND(T172="Completed",U172="Completed",V172="Completed",W172="Completed"), "Completed",IF(AND(T172="Completed",U172="InProgress",V172="InProgress",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Completed",V172="InProgress",W172="Not Started"),"InProgress",IF(AND(T172="Not Started",U172="Not Started",V172="Not Started",W172="Not Started"),"Not Started",IF(AND(T172="InProgress",U172="Not Started",V172="Not Started",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Not Started",V172="Not Started",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Completed",V172="Not Started",W172="Not Started"),"InProgress",IF(AND(T172="Completed",U172="Completed",V172="Completed",W172="Not Started"),"InProgress", IF(AND(T172="Completed",U172="Completed",V172="Completed",W172="Completed"), "Completed",IF(AND(T172="Completed",U172="InProgress",V172="InProgress",W172="Not Started"),"InProgress",IF(AND(T172="Not in Scope",U172="Not in Scope",V172="Not in Scope",W172="Not in Scope"),"Not in Scope", IF(AND(T2="Completed",U2="Completed",V2="Completed",W2="InProgress"),"InProgress","Invalid Combination")))))))))))))))))
Top Expert 2008

Commented:
This includes your latest change:

=IFERROR(INDEX({"Not Started","In Progress","In Progress","In Progress","In Progress","Completed","In Progress","In Progress","In Progress","Completed","Not in Scope"},MATCH(MATCH(LEFT(T2,5),{"Not S","InPro","Compl","Not i"},0)&MATCH(LEFT(U2,5),{"Not S","InPro","Compl","Not i"},0)&MATCH(LEFT(V2,5),{"Not S","InPro","Compl","Not i"},0)&MATCH(LEFT(W2,5),{"Not S","InPro","Compl","Not i"},0),{"1111","2111","3111","3311","3331","3333","3221","3321","3332","3333","4444"},0)),"Inappropriate Status Combination")

Kevin