Formula for formula field looking at multiple picklist items

Question

I would be greatful if you could help.

I have a custom formula field "Acceptability Rating" and i want it to look at two picklist fields "Status" and "Ruled out reason" and from the results in those fields determine the Acceptability rating as Acceptable, Unacceptable or Undetermined.

There are 10 different status and there are 20 ruled out reasons and dependent of which picklist item is selected will determine the rating. I have managed to do a formula that looks at a single field and picklist value (see below) is there a quick and easy way to do multiple values in this scenario ?

example Status picklist items - "New", "LNC", "Unqulified", "Dealt"

example Ruled out Reason picklist items - "Lost contact", "bought elsewhere"

current formula - IF( ISPICKVAL( pba__Status__c , "New"), "Undetermined", "")

Answer 1

You will have to build a formula with nested IF conditions. Lets assume the outcome is to be determined as mentioned below:

Status = "New", Ruled out Reason = "Lost contact" => Acceptability rating = "Acceptable"

Status = "LNC", Ruled out Reason = "bought elsewhere" => Acceptability rating = "Unacceptable"

Status = "Unqualified" => Acceptability rating = "Undetermined"

Formula for Acceptability rating would be something like this (you can include AND or OR based on your requirement, also nested if required):

IF(AND(ISPICKVAL(pba__Status__c, "New"), ISPICKVAL(pba__Reason, "Lost contact")), "Acceptable", 
  IF(AND(ISPICKVAL(pba__Status__c, "LNC"), ISPICKVAL(pba__Reason, "bought elsewhere")), "Unacceptable", 
     IF(ISPICKVAL(pba__Status__c, "Unqualified"), "Undetermined", "")
  )
)

You will have to efficiently work out the # of conditions and outcomes to frame the formula. For limitations & restrictions on formula fields, refer here. Try not to make the formula too big. If there are too many nested IF conditions (or if the formula is huge), you might start see performance issues in long run (since the formula field values get evaluated at real time).