I have some formulas that use values stored in custom settings. For example If(X < custom setting-y && x >= custom setting , 'foo', 'bar') My formula seems to be fine and my custom setting values are correct but I am getting bar instead of foo.

    If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_A_Value__c, 'A', 
If(Email_Open_Rate__c < $Setup.health_check_console__c.Email_Open_Rate_A_Value__c && Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_B_Value__c, 'B', 
If(Email_Open_Rate__c < $Setup.health_check_console__c.Email_Open_Rate_B_Value__c && Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_C_Value__c, 'C', 
If(Email_Open_Rate__c < $Setup.health_check_console__c.Email_Open_Rate_C_Value__c && Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_D_Value__c, 'D', 

enter image description here

When I adjusted the value for C to .199 then my records with a grade of 20% were given a C.


The main feature of using nested if statements is that you don't need to recheck all of the previous conditions. Here's a revised version:

If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_A_Value__c, 'A', 
If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_B_Value__c, 'B',
If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_C_Value__c, 'C', 
If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_D_Value__c, 'D', 

Your formula looks otherwise correct, as far as I can tell, but this version should avoid any rounding errors, which I suspect may be what caused the situation at hand.

  • I think I am going to open a case. I tried your revised formula still no change. It seems like a rounding error but in this case, the percentage is 3 / 5. That comes out of 20% no matter how many decimals places I try to add. – Brooks Johnson Sep 17 '18 at 15:08
  • That's curious. Yeah, if the above formula isn't working, then it's definitely something wrong. I've used this formula at least a few times in the past with success, and I see no reason why it shouldn't. – sfdcfox Sep 17 '18 at 15:13

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.