[SalesForce] How do to currency conversion in formula field

I am trying to create formula field which will bucket based on the Amount in US $ currency.

If(Amount < 25000, "<25K", 
If(Amount > 25000 && Amount < 100000, "25-100K", 
If(Amount > 150000, ">150K", "100-150K")))

In the above example, I want to convert the Amount to US$ and then compare it.

if Amount > US$ 25000, then value should be 25 K..
if Amount > US$ 25000 && Amount < US$100000, , then value should be 25-100K"

Can anyone suggest a solution..

Best Answer

You could create a Hierarchy Custom Setting to store your conversion rates to then access in formula fields. Note that you'd have to create a rate per profile.

Docs: Accessing Custom Settings -- one can only access Hierarchy Custom Settings in formula fields

Example:

  • Custom Setting Object Name: Currency_Conversion
  • Field: Number(13,5) Rate

Formula:

IF($Setup.Currency_Conversion__c.Rate__c * Amount__c < 25000, "<25K",
    IF($Setup.Currency_Conversion__c.Rate__c * Amount__c < 10000, "25-100K",
        ...
Related Topic