Forum Discussion

AMZI2001's avatar
AMZI2001
Copper Contributor
Jun 19, 2022
Solved

help

I need help putting the formula for the table below.

 

Target
Start Date		Actual CompletionResultStatus
   Not started
12-Jun-22  In Progress
12-Jun-2215-Jun-22FailRetest
12-Jun-2217-Jun-22PassCompleted

 

 

Target Start Date<Blank> ,Actual Completion <Blank> = Not Started

Target Start Date<Blank> ,Actual Completion <Blank>, Result <Blank> = In Progress

Target Start Date<Blank> ,Actual Completion <Blank>, Result <Pass> Completed

Target Start Date<Blank> ,Actual Completion <Blank>, Result <Fail> Retest

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jun 19, 2022

    AMZI2001 

    =IF(AND(A3="",B3="",C3=""),"Not started",IF(AND(A3<>"",B3="",C3=""),"In progress",IF(AND(A3<>"",B3<>"",C3="Fail"),"Retest",IF(AND(A3<>"",B3<>"",C3="Pass"),"Completed",""))))

    Maybe with this formula.

     

5 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 19, 2022

    AMZI2001 

    =IF(AND(A3="",B3="",C3=""),"Not started",IF(AND(A3<>"",B3="",C3=""),"In progress",IF(AND(A3<>"",B3<>"",C3="Fail"),"Retest",IF(AND(A3<>"",B3<>"",C3="Pass"),"Completed",""))))

    Maybe with this formula.

     

    • AMZI2001's avatar
      AMZI2001
      Copper Contributor
      Jun 19, 2022

      OliverScheurich 

       

      Thank you!

       

      I hv tried and its works.

       

      but there were additional condition added in the formula.

       

      help me to validate if this is correct.

       

      =IF(AND(A3="",B3="",C3=""),"Not started",IF(AND(A3<>"",B3="",C3=""),"In progress",IF(AND(A3<>"",B3<>"",C3="Pass",E3=""),"Completed",IF(AND(A3<>"",B3<>"",C3="Fail",E3=""),"Retest",IF(AND(A3<>"",B3<>"",C3="Fail",E3="Fail"),"Retest",IF(AND(A3<>"",B3<>"",C3="Fail",E3="Pass"),"Completed",""))))))

       

      Thank you

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jun 19, 2022

        AMZI2001 

        =IF(AND(A3="",B3="",C3=""),"Not started",IF(AND(A3<>"",B3="",C3=""),"In progress",IF(AND(A3<>"",B3<>"",C3="Pass",E3=""),"Completed",IF(AND(A3<>"",B3<>"",C3="Fail",E3=""),"Retest",IF(AND(A3<>"",B3<>"",C3="Fail",E3="Fail"),"Retest",IF(AND(A3<>"",B3<>"",C3="Fail",E3="Pass"),"Completed",""))))))

        You are welcome. Your formula works in my sheet.

         

    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Jun 19, 2022

      AMZI2001

      A little more modern:

      =IFS(AND(C3="Pass",B3>0,A3>0),"Completed",AND(C3="Fail",B3>0,A3>0),"Retest",AND(ISBLANK(C3),ISBLANK(B3),A3>0),"In Progress",AND(ISBLANK(C3),ISBLANK(B3),ISBLANK(A3)),"Not started",1,"")

       

      • AMZI2001's avatar
        AMZI2001
        Copper Contributor
        Jun 19, 2022
        Hi

        Thank You.

        I'll try this formula also. Let u know soon!

Resources