Aug 14 2018 04:54 AM
Hello All,
I have three formulas listed below and wanted to know if I can nest these three formulas in one cell/formula
Formula 1 =TRIM(C2&A2)
Formula 2 = =IF(D2="33g21","D",IF(D2="D289c","D",IF(D2="U289c","D",IF(D2="N289c","IC",IF(D2="33310","D",IF(D2="53g21","D",IF(D2="53310","D",IF(D2="C3310","D",IF(D2="C3g21","D",IF(D2="F3g21","D",IF(D2="F3310","D","F")))))))))))
Formula 3 = =(IF(OR(RIGHT(TRIM(C2),1)="X"),"No Pay Run",(IF(OR(TRIM(K2)=""),"MDI",(IF(OR(TRIM(J2)=""),"MDI",(IF(OR(TRIM(C2)="N"),"IC Pay Run",(IF(OR(RIGHT(TRIM(E2),1)="D"),"D","F"))))))))))
Attached excel file
Regards,
Chandrakanth.K
Aug 14 2018 05:42 AM
I think its this:
=TRIM(C2&A2) & IF(TRIM(C2&A2)="33g21","D",IF(TRIM(C2&A2)="TRIM(C2&A2)89c","D",IF(TRIM(C2&A2)="U289c","D",IF(TRIM(C2&A2)="N289c","IC",IF(TRIM(C2&A2)="33310","D",IF(TRIM(C2&A2)="53g21","D",IF(TRIM(C2&A2)="53310","D",IF(TRIM(C2&A2)="C3310","D",IF(TRIM(C2&A2)="C3g21","D",IF(TRIM(C2&A2)="F3g21","D",IF(TRIM(C2&A2)="F3310","D","F")))))))))))&(IF(OR(RIGHT(TRIM(C2),1)="X"),"No Pay Run",(IF(OR(TRIM(K2)=""),"MDI",(IF(OR(TRIM(J2)=""),"MDI",(IF(OR(TRIM(C2)="N"),"IC Pay Run",(IF(OR(RIGHT(TRIM(IF(TRIM(C2&A2)="33g21","D",IF(TRIM(C2&A2)="TRIM(C2&A2)89c","D",IF(TRIM(C2&A2)="U289c","D",IF(TRIM(C2&A2)="N289c","IC",IF(TRIM(C2&A2)="33310","D",IF(TRIM(C2&A2)="53g21","D",IF(TRIM(C2&A2)="53310","D",IF(TRIM(C2&A2)="C3310","D",IF(TRIM(C2&A2)="C3g21","D",IF(TRIM(C2&A2)="F3g21","D",IF(TRIM(C2&A2)="F3310","D","F")))))))))))),1)="D"),"D","F"))))))))))
Its all a bit circular. You would be better off just hiding your 3 columns and combining the results into a 4th.
Aug 14 2018 06:17 AM
Hello Philip,
This is working fine but the end result should be either D or F but I am getting value as 33310DD its giving me combination of all three functions.
Regards,
Chandrakanth.K
Aug 14 2018 07:26 AM
Hehe, you asked for all 3 formulas to be combined..
Try this:
=TRIM(C2&A2) & (IF(OR(RIGHT(TRIM(C2),1)="X"),"No Pay Run",(IF(OR(TRIM(K2)=""),"MDI",(IF(OR(TRIM(J2)=""),"MDI",(IF(OR(TRIM(C2)="N"),"IC Pay Run",(IF(OR(RIGHT(TRIM(IF(TRIM(C2&A2)="33g21","D",IF(TRIM(C2&A2)="TRIM(C2&A2)89c","D",IF(TRIM(C2&A2)="U289c","D",IF(TRIM(C2&A2)="N289c","IC",IF(TRIM(C2&A2)="33310","D",IF(TRIM(C2&A2)="53g21","D",IF(TRIM(C2&A2)="53310","D",IF(TRIM(C2&A2)="C3310","D",IF(TRIM(C2&A2)="C3g21","D",IF(TRIM(C2&A2)="F3g21","D",IF(TRIM(C2&A2)="F3310","D","F")))))))))))),1)="D"),"D","F"))))))))))
Aug 14 2018 07:43 AM
Hello Philip,
I tried but still I am getting the result as 33310D instead of "D". The Formula is give me the result along with concatenated result as well.
Regards,
Chandrakanth.K
Aug 14 2018 07:45 AM
SolutionJust remove the bit before the &
=(IF(OR(RIGHT(TRIM(C2),1)="X"),"No Pay Run",(IF(OR(TRIM(K2)=""),"MDI",(IF(OR(TRIM(J2)=""),"MDI",(IF(OR(TRIM(C2)="N"),"IC Pay Run",(IF(OR(RIGHT(TRIM(IF(TRIM(C2&A2)="33g21","D",IF(TRIM(C2&A2)="TRIM(C2&A2)89c","D",IF(TRIM(C2&A2)="U289c","D",IF(TRIM(C2&A2)="N289c","IC",IF(TRIM(C2&A2)="33310","D",IF(TRIM(C2&A2)="53g21","D",IF(TRIM(C2&A2)="53310","D",IF(TRIM(C2&A2)="C3310","D",IF(TRIM(C2&A2)="C3g21","D",IF(TRIM(C2&A2)="F3g21","D",IF(TRIM(C2&A2)="F3310","D","F")))))))))))),1)="D"),"D","F"))))))))))
Aug 14 2018 07:48 AM
Hello Philip,
Thank you So much. :)
Regards,
Chandrakanth.K
Aug 14 2018 07:45 AM
SolutionJust remove the bit before the &
=(IF(OR(RIGHT(TRIM(C2),1)="X"),"No Pay Run",(IF(OR(TRIM(K2)=""),"MDI",(IF(OR(TRIM(J2)=""),"MDI",(IF(OR(TRIM(C2)="N"),"IC Pay Run",(IF(OR(RIGHT(TRIM(IF(TRIM(C2&A2)="33g21","D",IF(TRIM(C2&A2)="TRIM(C2&A2)89c","D",IF(TRIM(C2&A2)="U289c","D",IF(TRIM(C2&A2)="N289c","IC",IF(TRIM(C2&A2)="33310","D",IF(TRIM(C2&A2)="53g21","D",IF(TRIM(C2&A2)="53310","D",IF(TRIM(C2&A2)="C3310","D",IF(TRIM(C2&A2)="C3g21","D",IF(TRIM(C2&A2)="F3g21","D",IF(TRIM(C2&A2)="F3310","D","F")))))))))))),1)="D"),"D","F"))))))))))