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)))
)