IF function problem

[OSS]

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. 

[PattemManohar · Accepted solution]

@OSS Please try this..

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

[PattemManohar · Accepted solution]

@OSS Please try this..

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

[Anonymous · Accepted solution]

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

[PattemManohar]

@Anonymous @OSS

Thanks for that !! But anyway, the second parameter for LEFT function is optional. By default it will take 1 if we don't specify.

[Anonymous]

cheers ok, i didn't know that

thanks

[OSS]

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.

[OSS]

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

That is why my formula wasn't accepted 🙂