The Requirement: When the user selects a value in a picklist field, a formula field should store a numerical value.

The Problem: The formula I have written ALWAYS returns the value 0, regardless of the picklist value selected. See attached screenshots for proof of the 1) picklist values and 2) the record I am testing with.

I have no idea what I'm doing wrong. I've done this before several times and consulted the documentation. By all accounts, this formula should be working.

I have tried variations of this formula using TEXT(), IF() and ISPICKVAL(). None of them seem to work -- they all return the exception case ("0").

Here is my formula:

"Almost no impact", 1,
"Minor impact", 2,
"Moderate", 3,
"Significant impact", 4,
"Cause early termination or major problems", 5,

Here are the picklist Values... Picklist Values Screenshot

And here is the record I am testing with... Test Record Screenshot

1 Answer 1


Okay I have finally resolved the issue!

The solution is bizarre and makes no sense to me. I directly copied and pasted the API name values from the picklist into the formula field. There was absolutely no difference in the values, but for some reason that seems to have worked.

  • 1
    Probably you had non-printable characters before you copy pasted the API names. Jun 22, 2020 at 13:33
  • @HengkyIlawan oh that's interesting... I'm not sure what counts as a "non-printable" character, but as per the screenshots all my picklist values were plain English words. What do you mean by non-printable?
    – JustinF
    Jun 22, 2020 at 16:57
  • @JustinF don't forget to mark your answer as correct
    – Novarg
    Jul 7, 2022 at 7:11

You must log in to answer this question.

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