1

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

1
  • 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 '14 at 15:23
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

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",
        ...
2
  • 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 '14 at 15:53
  • Try having a field to denote Currency Type and reference it in the formula – Scott Pelak Sep 26 '14 at 16:05
0

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.

Reference

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.