Formula not working for me

[Karl_Spratt]

Hi Community,

I want to say if the currency  code  = Eur then multiply the Ex OH Cost column by 1.069518717 or if the currency  code  = GBP then multiply the Ex OH Cost column  by  1.204529029 if the Currency Code is not EUR or GBP use the Ex OH Cost

IF [CURRENCY_CODE] = 'EUR' THEN [Ex OH Cost] * "1.069518717" ELSEIF [CURRENCY_CODE] = 'GBP' THEN [Ex OH Cost] * "1.204529029" ELSE [Ex OH Cost] ENDIF

But I'm get a parse error I've tried double quotes "" and Single '' but neither work when try finish with the ELSE [Ex OH Cost] ENDIF part of the formula. 

See attached error.

Can someone advise the fix here please.

TIA 

Karl. 

[ShankerV · Accepted solution]

Hi @Karl_Spratt 

Please use the below

IF [CURRENCY_CODE] = 'EUR' THEN [Ex OH Cost] * 1.069518717 ELSEIF [CURRENCY_CODE] = 'GBP' THEN [Ex OH Cost] * 1.204529029 ELSE [Ex OH Cost] ENDIF

Many thanks

Shanker V

[Qiu]

@Karl_Spratt 
I agree with @ShankerV  and we should make sure the column of [Ex OH Cost] is of numeric.

[ShankerV]

Hi @Karl_Spratt 

If your column is of non-numeric data type, then use the below formula.

Irrespective of the datatype, the below formula will work as tonumber() helps to convert from string to numeric datatype.

IF [CURRENCY_CODE] = 'EUR'
THEN tonumber([Ex OH Cost]) * 1.069518717
ELSEIF [CURRENCY_CODE] = 'GBP'
THEN tonumber([Ex OH Cost]) * 1.204529029
ELSE tonumber([Ex OH Cost])
ENDIF

Many thanks

Shanker V

[Karl_Spratt]

Thanks Guys the formula you provide worked. 

Cheers,

Karl.