Nested IF

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
ioechoAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
=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"))))))))))))))))
0
 
Codrut TambuSr. Software Developer EngineerCommented:
Use semicolon (;) instead of comma(,) like:
=IF(1<2;"TRUE";"FALSE")
=IF([logical_condition];[value_if_true];[value_if_false])
0
 
ioechoAuthor 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"))))))))))))))))
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ioechoAuthor Commented:
Perfect! Thank you so much!
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
ioechoAuthor 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")))))))))))))))))
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.