1

In my lead based on my Annual revenue, have to update a custom field as

'high' 'medium' 'low'

so created a custom formula field priority i have to update the field based on the condition like

if(( AnnualRevenue > 10000) -High, if(( AnnualRevenue > 1000) -medium if(( AnnualRevenue < 1000) -low

Thought of using a formula field

3 Answers 3

2

Using chained if() functions in a formula would work, but there is another approach, the case() function.

In the end, it accomplishes the same thing. The tricky part here is that case() isn't really built to make comparisons other than caseVariable == <value>. Lucky for us, though, your chosen values are powers of 10. That means we can break this comparison down using the log() function in conjunction with case().

Case(FLOOR(LOG(AnnualRevenue)),
    0, 'Low',
    1, 'Low',
    2, 'Low',
    3, 'Medium',
    'High'
)

LOG(AnnualRevenue) takes the logarithm (base 10) of the argument, meaning the result will be 2 if AnnualRevenue is 100, 3 if it's 1000, and somewhere between 2 and 3 if AnnualRevenue is, say, 343.

Taking the FLOOR() of the result means that even if AnnualRevenue isn't a perfect power of 10, we'll still get an integer as a result. FLOOR(LOG(343)) results in 2.

This would be equivalent to

if(
    AnnualRevenue < 1000, 
    'Low', if(
               AnnualRevenue >= 1000, 'Medium', if(
                                                    AnnualRevenue >= 10000, 
                                                    'High', ''
                                                )
     )
)

Note that you need to be careful to ensure that your ranges are continuous. If you switch between testing for strictly less than (AnnualRevenue < 1000) and strictly greater than (AnnualRevenue > 1000), you have a point discontinuity in your range at AnnualRevenue == 1000. If your AnnualRevenue is exactly 1000, the result of the formula would be undefined.

This approach will run into issues with negative values of AnnualRevenue and any value of AnnualRevenue between 0 and 1. We can fix that with a little more work.

Case(FLOOR(LOG(MAX(1, AnnualRevenue))),
    0, 'Low',
    1, 'Low',
    2, 'Low',
    3, 'Medium',
    'High'
)

MAX(1, AnnualRevenue) takes care of negative values of AnnualRevenue and 0 (both of which the log function is not defined for). It also takes care of revenue values greater than 0 but less than 1 (for which the log would be a negative number).

In the end, it's probably simpler to just nest if() functions for this one, but exercises like this help keep my programming mind sharp.

1
  • 1
    IF( AnnualRevenue > 10000, 'High', IF( AnnualRevenue>= 1000, 'Medium', IF( AnnualRevenue<= 1000, 'low', null) ) ) mismatched parentheses, just giving an update - thanks for your time @Derek
    – SFDC
    Oct 16, 2017 at 7:01
2


Please try this formula.Hope it will help you.

IF( AnnualRevenue > 10000, 'High',  IF( AnnualRevenue> 1000, 'Medium',  IF( AnnualRevenue< 1000, 'low', null) ) )

Regards,
Ajay

1
  • Yeah I got it Ajay, -thanks for your time it helped
    – SFDC
    Oct 16, 2017 at 7:01
1

First create a formula field with return type Text and then update your formula. Also in last If condition you want to use = because if you get 1000 then none of your condition will be satisfied.

if(( AnnualRevenue  > 10000) ,'High',
if(( AnnualRevenue  > 1000) ,'medium'
if(( AnnualRevenue  <= 1000) ,'low','')))

You must log in to answer this question.

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