1 Reply Latest reply on Oct 31, 2018 4:30 PM by Shinichiro Murakami

    Help With Calculation

    Johnny K

      Hello Guys

       

      i am a little bit stuck with few calculation, unable to find a proper solution.

       

      PFA excel sheet and Tableau Workbook.(2018.2.2)

       

      Need to write calculation for below mentioned fields in Excel

      1) A in Months ( Column X in Excel)--->   =IF(O2="G",(S2-K2)/30,X3) ( i was able to get this calculation but in excel they are showing in decimals but iam not getting in decimals)

       

      2) A in Days (Column Y in Excel)------->   =IF(O2="G",S2-K2,Y3)         (This i was able to achieve)

       

      3) Time Days Column AA in Excel)----> 

      =IF(AND($M2="NO",$O2="A",$S2<>"#"),MAX(0,$S2-$K2),IF(AND($M2="NO",$O2="A",$R2<>"#"),MAX(0,$R2-$K2),IF(AND($M2="NO",$O2="A",$Q2<>"#"),MAX(0,$Q2-$K2),IF(AND($M2="NO",$O2="A",$P2<>"#"),MAX(0,$P2-$K2),IF(AND($M2="YES",$O2="A"),$B$2-$K2,IF(AND($M2="NO",$O2="A",$P2="#",$Q2="#",$R2="#",$S2="#"),"No completion date recorded","N/A"))))))

       

      4) Status 2 (Column AB in Excel)----->  =IF(AND(AA2<>"N/A",AA2<>"No completion date recorded"),IF(AA2>Y2,"MISS","HIT"),"N/A")

       

      5) Landing Date (Column AC in Excel)-----> =IF(AND($M2="NO",$O2="A"),IF($S2<>"#",$S2,IF($R2<>"#",$R2,IF($Q2<>"#",$Q2,IF($P2<>"#",$P2,$B$3)))),IF(AND($M2="YES",$O2="A"),$S3,$B$3))

       

      Thank You any help is appreciated.

       

      Regards

      Johnny.