SOLVED

help

New Contributor

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

5 Replies
best response confirmed by AMZI2001 (New Contributor)
Solution

@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.

not started in progress.JPG 

@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,"")

 

@Quadruple_Pawn 

 

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

Hi

Thank You.

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

@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.

target start date.JPG