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

  • We dont have multi currency enabled. I don't think i would get approval to turn on the multi currency just to meet this requirements
    – user5352
    Sep 26, 2014 at 15:23

2 Answers 2


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


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


IF($Setup.Currency_Conversion__c.Rate__c * Amount__c < 25000, "<25K",
    IF($Setup.Currency_Conversion__c.Rate__c * Amount__c < 10000, "25-100K",
  • Thanks for your suggestion...But in my case I need to look at Opportunity currency.. if the Opportunity currency is other then USD then I need to convert the opportunity amount into USD and then compare it for bucketing..Since within the same profile users could have different opportunity currency,..i am not sure if setting the custom setting at the profile level would work...
    – user5352
    Sep 26, 2014 at 15:53
  • Try having a field to denote Currency Type and reference it in the formula Sep 26, 2014 at 16:05

It pretty old post but thought folks navigating here just as I did will certainly benefit ...

IF( Amount * (1/CURRENCYRATE(TEXT(CurrencyIsoCode))) < 25000 , "<25K",
    IF(Amount * (1/CURRENCYRATE(TEXT(CurrencyIsoCode))) >= 25000 &&
        Amount * (1/CURRENCYRATE(TEXT(CurrencyIsoCode))) < 100000, "25-100K", 
            IF(Amount * (1/CURRENCYRATE(TEXT(CurrencyIsoCode))) > 150000, ">150K", "100-150K") ))

CURRENCYRATE Return the conversion rate to the corporate currency for the given CurrencyIsoCode, or 1.0 if the currency is invalid.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .