Custom form calculated field - Help

[RichardHu2]

Can anyone see any issues with the calculation below. Formula aims to convert a Local currency into GBP Sterling. User inputs value in local currency (New Credit Limit), selects currency from a drop down field e.g. AUD - Australian Dollar and the formula should divide local currency by exchange rate.

I've tried so many iterations but i just get the very unhelpful red box. Any guidance much appreciated

ROUND(IF(Currency="AUD - Australian Dollar",DIV({New Credit Limit},{AUD - Exchange rate}),

IF(Currency="EUR - Euro",DIV({New Credit Limit},{EUR - Exchange rate}),

IF(Currency="USD - US Dollar",DIV({New Credit Limit},{USD - Exchange rate}),

IF(Currency="CHF - Swiss Franc",DIV({New Credit Limit},{CHF – Exchange rate}),

IF(Currency="HKD - Hong Kong Dollar",DIV({New Credit Limit},{HKD – Exchange rate}),

IF(Currency="CZK - Czech Koruna",DIV({New Credit Limit},{CZK - Exchange rate}),

IF(Currency="JPY - Japanese Yen",DIV({New Credit Limit},{JPY - Exchange rate}),

IF(Currency="GBP - Great British Pound",DIV({New Credit Limit},{GBP - Exchange rate}),

IF(Currency="RMB - Chinese Yuan Renminbi",DIV({New Credit Limit},{RMB - Exchange rate},""))))))))),0)

[skyehansen]

quick question - I noticed the CHF and HKD exchange rates both have a longer (n-dash) dash instead of a hyphen. Is this really the case?

second quick question: did a shorter version of this calculation work and now you're having problems adding new rates to it, or is the entire thing always failing?

[RichardHu2]

Thanks Skye, yes good spot. Fixed that but still not working. I've tried shortened versions of the formula with less currencies but to no avail, I'd reached the conclusion there must be something fundamentally wrong with my formula. For example the following doesn't work either

ROUND(IF(Currency="AUD - Australian Dollar",DIV({New Credit Limit},{AUD - Exchange rate}," "),0)

[skyehansen]

I'm getting results with the following:

ROUND(IF({Currency}="1",DIV(New Credit Limit,{Exchange Rate})," "),0)

Created in my system:

-Field called "Currency" with dropdown choices 1 and 2

-Number Field called New Credit Limit

-Number Field called Exchange Rate

-Calculated Number Field (calculation above)

Switching between 1 and 2, selecting 1 gives me a result, and selecting 2 gives me 0.