1

I have a custom object with 3 picklist fields. I want to sum up the values of the picklist fields and get an average of the number and round it down. In otherwords, if picklist one has a value of 3, picklist two has a value of 3, and picklist one has a value of 1, Then the average rounded down would be

3+3+1 / 3 = 2.333333 so the score would be "2".

Each individual picklist field contains the same text values (hot,warm,cold).

Each picklist value has a numerical counterpart that is NOT stored anywhere. So "Hot" equals 3, "Warm" equals 2 and "Cold" equals 1. Yes, it would be easier if the picklist values just has the numbers (3,2,1) instead of the text values (hot,warm,cold), but it's not this way for other reasons.

I'm trying create a formula to sum up the choices based on the picklist value and based on the value display an image representing "hot", "warm" or "cold", but my syntax is off. The 3 individual images are stored in salesforce.

Can someone please provide a formula that will do this based on my pseudo code below or different code if mine is unusable as a starting point? Easier with apex? I'm trying to avoid code if possible.

IF(  
ISPICKVAL(LeadTemp__c,"Hot"),3) +
ISPICKVAL(LeadTemp___c,"Warm"),2) +
ISPICKVAL(LeadTemp___c,"Cold"),1) +
ISPICKVAL(LeadTemp__c,"Unknown"),0,NULL)))
)

+ 

IF(  
ISPICKVAL(LeadRating__c,"Hot"),3) +
ISPICKVAL(LeadRating___c,"Warm"),2) +
ISPICKVAL(LeadRating___c,"Cold"),1) +
ISPICKVAL(LeadRating__c,"Unknown"),0,NULL)))
)

 + 

IF(  
ISPICKVAL(LeadRating__c,"Hot"),3) +
ISPICKVAL(LeadRating___c,"Warm"),2) +
ISPICKVAL(LeadRating___c,"Cold"),1) +
ISPICKVAL(LeadRating__c,"Unknown"),0,NULL)))
)
Improve this question

1 Answer 1

Reset to default
2

Your code will be simpler with CASE formulas. With a picklist, though, you first have to get the TEXT of the field value.

FLOOR((
    CASE(TEXT(Picklist1__c), "Hot", 3, "Warm", 2, "Cold", 1, 0) +
    CASE(TEXT(Picklist2__c), "Hot", 3, "Warm", 2, "Cold", 1, 0) +
    CASE(TEXT(Picklist3__c), "Hot", 3, "Warm", 2, "Cold", 1, 0)
)/3)

Some sample input/output combos:

"Hot" + "Warm" + "Cold" = 2
"Hot" + "Hot" + "Hot" = 3
"Warm" + "Warm" + Cold" = 1
"Hot" + "Unknown" + "Unknown" = 1

If you want to only count the specified values above against the average, I would create a separate formula called Rating_Count__c.

IF(NOT(ISPICKVAL(Picklist1__c, "")), 1, 0) + 
IF(NOT(ISPICKVAL(Picklist2__c, "")), 1, 0) + 
IF(NOT(ISPICKVAL(Picklist3__c, "")), 1, 0)

The reason this needs to be a separate formula is to avoid divide by zero errors. Now you can modify the initial formula as follows:

IF(Rating_Count = 0, NULL, FLOOR((/*sum*/)/Rating_Count__c)
Improve this answer
4
  • One thing, the code you provide works, but if the value is 0 it factors it into the average which throws it off. If the value is 0 for a given picklist than it should be excluded. In other words, when doing the division to get the average, it shouldn't include picklist values that equate to 0.
    – user1669296
    Jun 2, 2016 at 4:46
  • @user1669296 Something like above?
    – Adrian Larson ♦
    Jun 2, 2016 at 17:29
  • It works, but running into the error: Error: Compiled formula is too big to execute (8,350 characters). Maximum size is 5,000 characters. Not your fault at all, I guess I will have to do this in apex?
    – user1669296
    Jun 2, 2016 at 17:52
  • You could reduce it perhaps...if just counting any non-empty value works for you. @user
    – Adrian Larson ♦
    Jun 2, 2016 at 18:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .