How to create formula with choices list?

Question

I want to create a priorization system within my opportunities. It should be a field showing the output of amount * probability * boost-factor. Boost factor is supposed to be a gut feeling about the opportunity. It only has three values, 0.75 for a slightly negative impression ,1 for a normal impression and 1.25 for a good feeling.

I tried to set up a custom field for boost, with a picklist with three values. The user should choose the boost factor according to her impression of the opportunity. I then created a new formula field Priority, which should compute the above mentioned output. However, picklists are not supported, when holding anything else but text (Link).

How can I set up my priorities, if not with a picklist?

Answer 1

You can go ahead and have those values as text in the picklist, then when it comes to your formula convert them using the function VALUE(TEXT(myPicklistField__c)). The text function converts the picklist into text, the value converts a text string that represents a number to a number. Does that help?

Your formula would look something like

amount__c * probability__c * VALUE(TEXT(boost-factor__c))

As a side note, if you wanted the user to choose from text values in the picklist, you could do a conversion to numeric factor in the formula itself.

Suppose you have the three picklist values 'Negative', 'Normal', 'Good' then you could rewrite your formula field as

amount__c * probability__c * CASE(Boost_Factor__c, 'Negative', 0.75, 'Normal', 1, 'Good', 1.25, 1)   //final argument is the default if no match