Forum Discussion

Chandrakanth K's avatar
Chandrakanth K
Copper Contributor
Jul 23, 2018

Support with If Function

Hello All,

 

I have written below if function to derive the payment type for vendors. I am stuck with one condition. If my column "I" is blank and if my column "C" is not equal to "C" or "K" then the result should be "MDI" I was able to add condition for "C" but unable to add condition for "K"

 

=(IF(OR(RIGHT(TRIM(C3),1)="9",RIGHT(TRIM(C3),1)="X"),"No Pay Run",(IF(AND(TRIM(I3)="",LEN(TRIM(C3))>1,(TRIM(C3))<>"C"),"MDI",(IF(AND(TRIM(I3)="",(TRIM(C3))<>"C"),"MDI",(IF(OR(TRIM(C3)="GM"),"IC Pay Run",(IF(OR(RIGHT(TRIM(C3),1)="T",RIGHT(TRIM(C3),1)="U"),"F",(IF(OR(RIGHT(TRIM(C3),1)="C",RIGHT(TRIM(C3),1)="D",RIGHT(TRIM(C3),1)="P",RIGHT(TRIM(C3),1)="Q",RIGHT(TRIM(C3),1)="E",RIGHT(TRIM(C3),1)="B",RIGHT(TRIM(C3),1)="X",RIGHT(TRIM(C3),1)="S",RIGHT(TRIM(C3),1)="K"),"D","MDI"))))))))))))

Attached sample excel report. Please help

 

 

Regards,

Chandrakanth.K

Formulas & Functions
Need Help

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 23, 2018

    Hi,

     

    Perhaps something like this

    =IF(OR(RIGHT(TRIM(C2),1)="9",
      RIGHT(TRIM(C2),1)="X"),"No Pay Run",
   IF(AND(TRIM(I2)="",
      LEN(TRIM(C2))=1,
      OR(TRIM(C2)<>"C",
         TRIM(C2)<>"K")),"MDI",
   IF(OR(TRIM(C2)="GM"),"IC Pay Run",
   IF(OR(RIGHT(TRIM(C2),1)="T",
      RIGHT(TRIM(C2),1)="U"),"F",
   IF(OR(RIGHT(TRIM(C2),1)="C",
      RIGHT(TRIM(C2),1)="D",
      RIGHT(TRIM(C2),1)="P",
      RIGHT(TRIM(C2),1)="Q",
      RIGHT(TRIM(C2),1)="E",
      RIGHT(TRIM(C2),1)="B",
      RIGHT(TRIM(C2),1)="X",
      RIGHT(TRIM(C2),1)="S",
      RIGHT(TRIM(C2),1)="K"),"D","MDI")
))))

    if not simplify the rest

     

Resources