Forum Discussion

Chandrakanth K's avatar
Chandrakanth K
Copper Contributor
Aug 14, 2018
Solved

nexting multiple Function

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",I...
excel
Formulas and Functions
  • Philip West's avatar
    Philip West
    Aug 14, 2018

    Just 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"))))))))))

Philip West's avatar
Philip West
Iron Contributor
Aug 14, 2018

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.

  • Chandrakanth K's avatar
    Chandrakanth K
    Copper Contributor
    Aug 14, 2018

    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

    • Philip West's avatar
      Philip West
      Iron Contributor
      Aug 14, 2018

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

      • Chandrakanth K's avatar
        Chandrakanth K
        Copper Contributor
        Aug 14, 2018

        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

Resources