# Multiple Condition in formula field

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

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.

• 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

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

Regards,
Ajay

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

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','')))
``````