+ Start a Discussion
SureshSuresh 

Reducing multiple CASE()

Hi All,

Can i know how to reduce the multiple case () in formula fields .

for EX:

CASE(X,"YES",1 ,-1)+
CASE(Y,"YES",1,0)+
CASE(Z,"YES",1,-1)


so,for this kind of formula can i know what is the good approach because as i am using multiple CASE() , i am getting  following error
"Compiled formula is too big to execute (5,100 characters). "
SwarnasankhaSwarnasankha

Hi Suresh,

One way of reducing the character could would be to use the IF() function. In case the field you are referring to is a picklist then you could use it like IF(TEXT(X) = "YES", 1, -1); however, this may not solve your problem at all because if you are using cases and if the fields you are referring to are just text or picklist fields then I am inclined to believe that your formula will be having atleast 60-70 occurances of CASE().

The IF() statement reduces the character count very marginally and if the reference fields are also a formula field then you will hit the same road block. Could you give me the business case for the need of such a calculated field - just guessing if you are using it to calculate an overall score of some kind based on inputs/responses captured against a set of questions?

It would be good to know how many fields you are using in this equation and if the references in the CASE() statement are just simple fields or calculative formulas.

Kind Regards,
Swarna.