+ Start a Discussion
Taylor Figge 10Taylor Figge 10 

Help reducing characters in field formula

I've created this field formula, but it exceeds the character limit. can anyone help me reduce the characters?

IF( AND( Industry ="Agriculture", Emp_Range__c ="1-4"),"1.0"),IF( AND( Industry ="Agriculture", Emp_Range__c ="5-9"),"1.0"),IF( AND( Industry ="Agriculture", Emp_Range__c ="10-24"),"1.2"),IF( AND( Industry ="Agriculture", Emp_Range__c ="25-49"),"1.5"),IF( AND( Industry ="Agriculture", Emp_Range__c ="50-99"),"2.1"),IF( AND( Industry ="Agriculture", Emp_Range__c ="100-249"),"2.6"),IF( AND( Industry ="Agriculture", Emp_Range__c ="250+"),"3.0"), IF( AND( Industry ="Banking", Emp_Range__c ="1-4"),"1.1"),IF( AND( Industry ="Banking", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Banking", Emp_Range__c ="10-24"),"1.5"),IF( AND( Industry ="Banking", Emp_Range__c ="25-49"),"1.9"),IF( AND( Industry ="Banking", Emp_Range__c ="50-99"),"2.9"),IF( AND( Industry ="Banking", Emp_Range__c ="100-249"),"3.4"),IF( AND( Industry ="Banking", Emp_Range__c ="250+"),"6.2"),IF( AND( Industry ="Construction", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Construction", Emp_Range__c ="5-9"),"1.2"),IF( AND( Industry ="Construction", Emp_Range__c ="10-24"),"1.3"),IF( AND( Industry ="Construction", Emp_Range__c ="25-49"),"1.4"),IF( AND( Industry ="Construction", Emp_Range__c ="50-99"),"2.1"),IF( AND( Industry ="Construction", Emp_Range__c ="100-249"),"2.2"),IF( AND( Industry ="Construction", Emp_Range__c ="250+"),"4.1"),IF( AND( Industry ="Consulting", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Consulting", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Consulting", Emp_Range__c ="10-24"),"1.4"),IF( AND( Industry ="Consulting", Emp_Range__c ="25-49"),"1.9"),IF( AND( Industry ="Consulting", Emp_Range__c ="50-99"),"2.0"),IF( AND( Industry ="Consulting", Emp_Range__c ="100-249"),"3.0"),IF( AND( Industry ="Consulting", Emp_Range__c ="250+"),"5.0"),IF( AND( Industry ="Education", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Education", Emp_Range__c ="5-9"),"1.5"),IF( AND( Industry ="Education", Emp_Range__c ="10-24"),"1.7"),IF( AND( Industry ="Education", Emp_Range__c ="25-49"),"1.9"),IF( AND( Industry ="Education", Emp_Range__c ="50-99"),"3.2"),IF( AND( Industry ="Education", Emp_Range__c ="100-249"),"4.3"),IF( AND( Industry ="Education", Emp_Range__c ="250+"),"5.0"),IF( AND( Industry ="Energy", Emp_Range__c ="1-4"),"1.1"),IF( AND( Industry ="Energy", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Energy", Emp_Range__c ="10-24"),"1.5"),IF( AND( Industry ="Energy", Emp_Range__c ="25-49"),"2.3"),IF( AND( Industry ="Energy", Emp_Range__c ="50-99"),"3.0"),IF( AND( Industry ="Energy", Emp_Range__c ="100-249"),"3.7"),IF( AND( Industry ="Energy", Emp_Range__c ="250+"),"6.7"),IF( AND( Industry ="Engineering", Emp_Range__c ="1-4"),"1.3"),IF( AND( Industry ="Engineering", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Engineering", Emp_Range__c ="10-24"),"1.5"),IF( AND( Industry ="Engineering", Emp_Range__c ="25-49"),"1.6"),IF( AND( Industry ="Engineering", Emp_Range__c ="50-99"),"2.2"),IF( AND( Industry ="Engineering", Emp_Range__c ="100-249"),"3.3"),IF( AND( Industry ="Engineering", Emp_Range__c ="250+"),"6.0"),IF( AND( Industry ="Entertainment", Emp_Range__c ="1-4"),"1.0"),IF( AND( Industry ="Entertainment", Emp_Range__c ="5-9"),"1.0"),IF( AND( Industry ="Entertainment", Emp_Range__c ="10-24"),"1.2"),IF( AND( Industry ="Entertainment", Emp_Range__c ="25-49"),"1.4"),IF( AND( Industry ="Entertainment", Emp_Range__c ="50-99"),"1.9"),IF( AND( Industry ="Entertainment", Emp_Range__c ="100-249"),"2.0"),IF( AND( Industry ="Entertainment", Emp_Range__c ="250+"),"3.8"),IF( AND( Industry ="Finance", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Finance", Emp_Range__c ="5-9"),"1.2"),IF( AND( Industry ="Finance", Emp_Range__c ="10-24"),"1.5"),IF( AND( Industry ="Finance", Emp_Range__c ="25-49"),"2.3"),IF( AND( Industry ="Finance", Emp_Range__c ="50-99"),"3.5"),IF( AND( Industry ="Finance", Emp_Range__c ="100-249"),"3.7"), IF( AND( Industry ="Finance", Emp_Range__c ="250+"),"4.0"), IF( AND( Industry ="Government", Emp_Range__c ="1-4"),"1.6"),IF( AND( Industry ="Government", Emp_Range__c ="5-9"),"2.0"),IF( AND( Industry ="Government", Emp_Range__c ="10-24"),"2.2"),IF( AND( Industry ="Government", Emp_Range__c ="25-49"),"2.9"),IF( AND( Industry ="Government", Emp_Range__c ="50-99"),"3.1"),IF( AND( Industry ="Government", Emp_Range__c ="100-249"),"3.3"), IF( AND( Industry ="Government", Emp_Range__c ="250+"),"4.0"), IF( AND( Industry ="Healthcare", Emp_Range__c ="1-4"),"1.3"),IF( AND( Industry ="Healthcare", Emp_Range__c ="5-9"),"1.5"),IF( AND( Industry ="Healthcare", Emp_Range__c ="10-24"),"1.6"),IF( AND( Industry ="Healthcare", Emp_Range__c ="25-49"),"2.3"),IF( AND( Industry ="Healthcare", Emp_Range__c ="50-99"),"2.7"),IF( AND( Industry ="Healthcare", Emp_Range__c ="100-249"),"2.8"), IF( AND( Industry ="Healthcare", Emp_Range__c ="250+"),"8.5"), IF( AND( Industry ="Hospitality", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Hospitality", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Hospitality", Emp_Range__c ="10-24"),"1.7"),IF( AND( Industry ="Hospitality", Emp_Range__c ="25-49"),"2.3"),IF( AND( Industry ="Hospitality", Emp_Range__c ="50-99"),"3.9"),IF( AND( Industry ="Hospitality", Emp_Range__c ="100-249"),"4.2"), IF( AND( Industry ="Hospitality", Emp_Range__c ="250+"),"4.5"), IF( AND( Industry ="Insurance", Emp_Range__c ="1-4"),"1.0"),IF( AND( Industry ="Insurance", Emp_Range__c ="5-9"),"1.4"),IF( AND( Industry ="Insurance", Emp_Range__c ="10-24"),"1.6"),IF( AND( Industry ="Insurance", Emp_Range__c ="25-49"),"2.6"),IF( AND( Industry ="Insurance", Emp_Range__c ="50-99"),"3.0"),IF( AND( Industry ="Insurance", Emp_Range__c ="100-249"),"4.0"), IF( AND( Industry ="Insurance", Emp_Range__c ="250+"),"14.0"), IF( AND( Industry ="Manufacturing", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Manufacturing", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Manufacturing", Emp_Range__c ="10-24"),"1.4"),IF( AND( Industry ="Manufacturing", Emp_Range__c ="25-49"),"1.7"),IF( AND( Industry ="Manufacturing", Emp_Range__c ="50-99"),"2.2"),IF( AND( Industry ="Manufacturing", Emp_Range__c ="100-249"),"2.6"), IF( AND( Industry ="Manufacturing", Emp_Range__c ="250+"),"3.0"), IF( AND( Industry ="Not for Profit", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Not for Profit", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Not for Profit", Emp_Range__c ="10-24"),"1.4"),IF( AND( Industry ="Not for Profit", Emp_Range__c ="25-49"),"2.3"),IF( AND( Industry ="Not for Profit", Emp_Range__c ="50-99"),"3.4"),IF( AND( Industry ="Not for Profit", Emp_Range__c ="100-249"),"4.5"), IF( AND( Industry ="Not for Profit", Emp_Range__c ="250+"),"5.0"), IF( AND( Industry ="Other", Emp_Range__c ="1-4"),"1,2"),IF( AND( Industry ="Other", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Other", Emp_Range__c ="10-24"),"1.4"),IF( AND( Industry ="Other", Emp_Range__c ="25-49"),"1.8"),IF( AND( Industry ="Other", Emp_Range__c ="50-99"),"2.2"),IF( AND( Industry ="Other", Emp_Range__c ="100-249"),"2.7"), IF( AND( Industry ="Other", Emp_Range__c ="250+"),"4.7"), IF( AND( Industry ="Retail", Emp_Range__c ="1-4"),"1.2"),IF( AND( Industry ="Retail", Emp_Range__c ="5-9"),"1.3"),IF( AND( Industry ="Retail", Emp_Range__c ="10-24"),"1.3"),IF( AND( Industry ="Retail", Emp_Range__c ="25-49"),"1.6"),IF( AND( Industry ="Retail", Emp_Range__c ="50-99"),"1.6"),IF( AND( Industry ="Retail", Emp_Range__c ="100-249"),"2.3"), IF( AND( Industry ="Not for Profit", Emp_Range__c ="250+"),"5.6"), IF( AND( Industry ="Transportation", Emp_Range__c ="1-4"),"1.1"),IF( AND( Industry ="Transportation", Emp_Range__c ="5-9"),"1.2"),IF( AND( Industry ="Transportation", Emp_Range__c ="10-24"),"1.2"),IF( AND( Industry ="Transportation", Emp_Range__c ="25-49"),"1.5"),IF( AND( Industry ="Transportation", Emp_Range__c ="50-99"),"2.1"),IF( AND( Industry ="Transportation", Emp_Range__c ="100-249"),"2.3"), IF( AND( Industry ="Transportation", Emp_Range__c ="250+"),"2.8"),””))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 
 
santanu boralsantanu boral
This is an ideal scenario to use CASE statement which is more efficient.

Can you please try out something like this:
 
CASE(Industry),
"Agriculture", 
	(CASE(Emp_Range__c), 
	"1-4", "1.0",
	"5-9", "1.0",
	NULL)
"Banking", (CASE (Emp_Range__c),
		   "1-4", "1.0",
		   "5-9", "1.0",
		   NULL)
NULL)

Please mark this answer if it helps.
Taylor Figge 10Taylor Figge 10
Do I still need the IF statement or does the Case Statement work like an IF statement?
santanu boralsantanu boral
You can first try it out with the CASE statement without the IF statement the way I have shown you.

For more information on how to use CASE statement refer https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#CASE
santanu boralsantanu boral
Is that solved your problem?
Taylor Figge 10Taylor Figge 10
It keeps giving me this error:

Error: Compiled formula is too big to execute (6,970 characters). Maximum size is 5,000 characters

But when I copy the formula to word it gives me a character count less than 5,000 characters. Does CASE count for more characters on the backen of Salesforce?