# Formula Logic - Two picklist fields determining the result

I have two picklist fields; "Status" and "Ruled out reason".

I have a formula field called acceptability rating which is determined by the status and the ruled out reason fields. I have written the logic for the formula but it is too long and will not fit within the maximum limits for a formula field. Can anyone help with shortening the logic?

At the moment i am writing the logic for the ruled out reason status every time is there any way to reduce this. Apologies if this is a stupid question but i am stuck with it. Logic outlined below;

``````IF(ISPICKVAL( pba__Status__c , "New"), "Undetermined", "")
+IF(ISPICKVAL( pba__Status__c , "Initial"), "Undetermined", "")
+IF(ISPICKVAL( pba__Status__c , "LNC"), "Undetermined", "")
+IF(ISPICKVAL( pba__Status__c , "Unqualified"), "Undetermined", "")
+IF(ISPICKVAL( pba__Status__c , "Good Lead"), "Acceptable", "")
+IF(ISPICKVAL( pba__Status__c , "Prospect"), "Acceptable", "")
+IF(ISPICKVAL( pba__Status__c , "Dealt"), "Acceptable", "")
+IF(ISPICKVAL( pba__Status__c , "Uncontactable"), "Unacceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Agent")), "Unacceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Bought Elsewhere")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Brexit")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Change of Circumstances")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Commercial property only")), "Unacceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Completed property")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Completion time")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Concerned about exit")), "Acceptable", "")
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Coronavirus")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Didn't like development")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Existing enquiry is good lead/prospect")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Expensive")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Financial")), "Unacceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Freehold only")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Fund")), "Unacceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Had property terminated")), "Unacceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Investor services")), "Undetermined", "")
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Lack of management control")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Language Barrier")), "Unacceptable", "")
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Location")), "Acceptable", "")
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Lost Confidence")), "Acceptable", "")
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Lost contact")), "Undetermined", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Not a strong enough investment")), "Acceptable", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Not interested refused to talk")), "Unacceptable", "")
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Other")), "Undetermined", "")
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Remove from database")), "Unacceptable", "")
``````

Try this out. The CASE functions convert your picklist values to a numeric value and then the outcomes (0, 1, 2) are translated.

``````CASE(
CASE( TEXT(  pba__Status__c ),
"New", 0,
"Initial", 0,
"LNC", 0,
"Unqualified", 0,
"Prospect", 2,
"Dealt", 2,
"Uncontactable", 1,
"Ruled Out", 1,
0
)
+
CASE( TEXT( Ruled_Out_Reason__c)
"Agent", 0,
"Bought Elsewhere", 1,
"Brexit", 1,
"Change of Circumstances", 1,
"Commercial property only", 0,
"Completed property", 1,
"Completion time", 1,
"Coronavirus", 1,
"Didn't like development", 1,
"Existing enquiry is good lead/prospect", 1,
"Expensive", 1,
"Financial", 0,
"Freehold only", 1,
"Fund", 0,
"Investor services", -1,
"Lack of management control", 1,
"Language Barrier", 0,
"Location", 1,
"Lost Confidence", 1,
"Lost contact", -1,
"Not a strong enough investment", 1,
"Not interested refused to talk", 0,
"Other", -1,
"Remove from database", 0,
0
),
0, "Undetermined",
1, "Unacceptable",
2, "Acceptable",
"Error"
)
``````