Forum Discussion

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

Formulas.xlsx9 KB

excel

Formulas and Functions

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

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

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
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
  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
  - Philip West
    Iron Contributor
    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"))))))))))