How to assign and sum up picklist values

Question

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

Answer 1

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)