Forum Discussion

OSS's avatar
OSS
Helper III
7 years ago
Solved

IF function problem

Dear members

 

Actually I want to perform very simple task.

 

I have 'Account number' and 'Amount' column in my table. I would like to create new column with if condition

 

In Excel, it looks like If(left('Account Number',1)=1, 'Amount', if(left('Account Number',1)=2, 'Amount'*(-1),0)

 

If the first digit of account number is 1 then took the original amount, if the first digit of account number is 2 then took the reverse.

 

I couldn't formulate this condition. Please help me. 

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    OSS Please try this..

     

    NewAmount = IF(LEFT(Test181[Account],1)="1",Test181[Amount],IF(LEFT(Test181[Account])="2",Test181[Amount]*-1,0))

  • Anonymous's avatar
    Anonymous
    7 years ago

    hi,

     

    shouldnt it be like this:

     

    NewAmount = IF(LEFT(Test181[Account],1)="1",Test181[Amount],IF(LEFT(Test181[Account],1)="2",Test181[Amount]*-1,0))

     

6 Replies

Replies have been turned off for this discussion
  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    OSS Please try this..

     

    NewAmount = IF(LEFT(Test181[Account],1)="1",Test181[Amount],IF(LEFT(Test181[Account])="2",Test181[Amount]*-1,0))

    • OSS's avatar
      OSS
      Helper III
      7 years ago

      Thank you very much Pattem. I wrote the same formula but forgot ""

      That is why my formula wasn't accepted :)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      hi,

       

      shouldnt it be like this:

       

      NewAmount = IF(LEFT(Test181[Account],1)="1",Test181[Amount],IF(LEFT(Test181[Account],1)="2",Test181[Amount]*-1,0))

       

      • OSS's avatar
        OSS
        Helper III
        7 years ago

        Thank you for warning, Theo. 1 is forgotten in the formula. Thank you for correction. It would help to avoid confuse if anyone would like to use this formula.