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

 

 

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

7 Replies

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

Resources