+ Start a Discussion
Sheena QueridoSheena Querido 

Too many picklist values for IF and ISPICKVAL ??

I tried creating the following formula, but I got an error message that said there were too many characters in my formula. I'm trying to use the CONTAINS function, but I'm losing my formula ... any help is appreciated. 

IF(ISPICKVAL(Service_Type_del__c, "One-time"),
ABS((Cost__c * Probability) / 1),

IF(ISPICKVAL(Service_Type_del__c, "Yearly"),
ABS((Cost__c * Probability) / 1),

IF(ISPICKVAL(Service_Type_del__c, "Twice Yearly"),
ABS((Cost__c * Probability) / 2),

IF(ISPICKVAL(Service_Type_del__c, "Quarterly"),
ABS((Cost__c * Probability) / 4),

IF(ISPICKVAL(Service_Type_del__c, "Monthly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Service_Type_del__c, "Twice Monthly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Service_Type_del__c, "Weekly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Service_Type_del__c, "Twice Weekly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Service_Type_del__c, "Daily"),
ABS((Cost__c * Probability) / 12),

0
)))))))))
Amit Chaudhary 8Amit Chaudhary 8
Try to use case function
like below
CASE( Text(Service_Type_del__c) ,

"One-time", ABS((Cost__c * Probability) / 1),

"Yearly",ABS((Cost__c * Probability) / 1),

"Twice Yearly", ABS((Cost__c * Probability) / 2),
...
0)
Let us know if this will help you

 
Akhil AnilAkhil Anil
Hi Sheena,

I assume that the Cost__c field used is another formula field ? If so can you please post the formula for that field as well. The formula you posted is not big enough to exceed the character limit.
Sheena QueridoSheena Querido
Hi Akhil,

Yes, Cost__c is another formula field: 


IF(ISPICKVAL(Margin__c , "60"), 
ABS((Amount -(0.60 * Amount))), 

IF(ISPICKVAL(Margin__c , "50"), 
ABS((Amount -(0.50 * Amount))), 

IF(ISPICKVAL(Margin__c , "40"), 
ABS(( Amount -(0.40 * Amount))), 

IF(ISPICKVAL(Margin__c , "45"), 
ABS((Amount -(0.45 * Amount))), 

IF(ISPICKVAL(Margin__c , "35"), 
ABS((Amount -(0.35 * Amount))), 

IF(ISPICKVAL(Margin__c , "30"), 
ABS((Amount -(0.30 * Amount))), 


))))))
Sheena QueridoSheena Querido
Hi Amit,

I tried the case formula, but still got a similar error message: 
 
Error: Compiled formula is too big to execute (9,247 characters). Maximum size is 5,000 characters