Need formula for generating numeric value based upon text pick list value

[sandys]

I have four custom Account object pick lists. Each pick list text field has four values: No Risk, Low Risk, Medium Risk,and High Risk. Contractual__c is one of the four pick lists.

I have an other custom field "C-RiskValue__c" defined as a number. I need a formula to autogenerate a numeric value in this second field based upon the pick list value selected. I found several suggestions but just cannot get the syntax correct. Suggestions?

IF(ISPICKVAL(Contractual__c, "No Risk"), 0.00,
     IF(ISPICKVAL(Contractual__c, "Low Risk"), 0.01,
          IF(ISPICKVAL(Contractual__c, "Medium Risk"), 0.05,
               IF(ISPICKVAL(Contractual__c, "High Risk"), 0.50,

null))))

[shale]

Try using a CASE statement instead:

CASE(Contractual__c,
"Low Risk", 0.01,
"Medium Risk", 0.05,
"High Risk", 0.5,
0.0)

The default here returns 0, which would occur when the value was "No Risk" or if a picklist value was not selected.

[sandys]

I tried your suggestion and still get this syntax error: Field Contractual__c may not be used in this type of formula. The Contractual__c field is just a pick list.

[sandys]

I noticed on another posting that putting the field name in quotes so it is defined as text should fix the error Field Contractual __c may not be used in this type of formula. That did return "no syntax errors" and I was able to save:

CASE("Contractual__c", "Low Risk", 0.01, "Medium Risk", 0.05, "High Risk", 0.5, 0.0)

Unfortunately, the new field that should display the numeric value based upon the pick list field, shows nothing even if I modify to another pick list value.

[shale]

Wrapping the field name in quotes compares the string "Contractual__c" to "Low Risk" etc, which will never return a match. So while it may prevent a syntax error, it definitely won't do what you need it to do.

So back to the syntax error... that's very strange. Picklists should be allowed in a CASE function.

Check #1: You've verified that Contractual__c is a picklist?

Check #2: If Contractual__c is a picklist, try the same formula with a different picklist? You mentioned you had 4 of them on the page, so try another -- or create a test picklist. It doesn't matter if the values are different, we just want to figure out why the syntax error is happening.

[SFF]

Almost there. If Contractual__c is a picklist, you have to wrap it in TEXT():

CASE(TEXT(Contractual__c),
"Low Risk", 0.01,
"Medium Risk", 0.05,
"High Risk", 0.5,
0.0)