1

I want to develop a functional formula that will count 3 fields and gives me 3 different values depends of the final result.

Twitter_Fans__c + Facebook_Fans__c + Linkedin_Followers__c =< 5000 , should give 0
Twitter_Fans__c + Facebook_Fans__c + Linkedin_Followers__c >= 5000 , should give 5
Twitter_Fans__c + Facebook_Fans__c + Linkedin_Followers__c >= 10000 , should give 10

This is where i am now:

IF( Formula_SUM_Social__c  < 5000, 0,  IF(Formula_SUM_Social__c   >=  5000, 5,  IF(Formula_SUM_Social__c   >=  10000, 10, null) ) ) 

With the formula above the last IF doesn't give a value of 10.

Please advise.

3
  • The formatting change I had made was to put your formulas into code blocks which makes them easier for others to read.
    – BarCotter
    Nov 24, 2014 at 14:16
  • Thank you @BarCotter but the formula that was changed was not functional properly for values above 10k. It was giving value of 0 because both >=5k and >=10k had miscalculated values. Nov 24, 2014 at 14:17
  • Sorry, i didn't notice that the formula in your question.
    – BarCotter
    Nov 24, 2014 at 14:21

3 Answers 3

1

In your formula Formula_SUM_Social__c >= 5000 will always be true once Formula_SUM_Social__c is greater than 5,000 and 10,000

There are a different ways you could do this, such as the one below.

IF( Formula_SUM_Social__c  < 5000, 0,  
IF(Formula_SUM_Social__c   <  10000, 5, 
IF(Formula_SUM_Social__c   >=  10000, 10, 
null) ) ) 

Or if you wanted to keep your original IF statements you could reverse the order of them so that it will give the correct results

IF(Formula_SUM_Social__c   >=  10000, 10, 
IF(Formula_SUM_Social__c   >=  5000, 5, 
IF( Formula_SUM_Social__c  < 5000, 0,  
null) ) ) 
1
  • this is another way of my answer thanks for making my code smaller :) Nov 24, 2014 at 14:20
1

Working solution:

IF( Formula_SUM_Social__c > 0 && Formula_SUM_Social__c < 5000, 0, 
IF( Formula_SUM_Social__c >= 5000 && Formula_SUM_Social__c < 10000 , 5, 
IF( Formula_SUM_Social__c >= 10000, 10, null) ) )
0

You could simplify this to:

CASE( 
  FLOOR( Formula_SUM_Social__c / 5000 ), /* Round Formula_SUM_Social__c / 5000 down to nearest integer */
  0, 0, /* If integer result is 0 (0 <= Formula_SUM_Social__c < 5000), returns 0 */
  1, 5, /* If integer result is 1 (5000 <= Formula_SUM_Social__c < 10000), returns 5 */
  10 /* Any other result (Formula_SUM_Social__c >= 10000) returns 10 */
)

The value of this method is that if Formula_SUM_Social__c ever ends up becoming a more complex formula (say you add additional social media fields to your sum), you only reference its compile size once, rather than 5 times. And it also makes it easy to add additional levels, or change your variance (say you change it to 2500 instead of 5000).

You must log in to answer this question.

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