Switch formula help

[Michelle_King]

Hi I am not sure it is an IF formula or a switch formula that I need but I cannot work this one our for love nor money.

So I am trying to write a complex risk assessment and within it is a simple single select column where my options are

Improbable
Remote
Possible
Probable
Almost Certain

What I was hoping to do is have a column next to it that converts the different options into numbers 1-5 (which I would then hide but use in calculations.) There are lots of different versions of this within the base but for the life of me I cannot work out how to take the select options and get it to return a number am I going crazy? Any help greatfully recieved xx

[Jeremy_Oglesby · Accepted solution]

Yes, @Michelle_King, a SWITCH() function would work great here:

SWITCH(
   {Single Select Column Name},
   "Improbable", 1,
   "Remote", 2,
   "Possible", 3,
   "Probable", 4,
   "Almost Certain", 5
)

[Michelle_King]

Thank you but it’s returning the same problem I had with the if formula :frowning: the improbable returns 1 fine but none of the other options are returning a value)

[kuovonne]

Can you share a screen capture of the configuration of your single select field? Maybe there are some slight differences in case or spacing in your field.

[Michelle_King]

Thanks I’m sure its something silly that I am doing xxx

[Jeremy_Oglesby]

That seems odd – I don’t see anything wrong with your formula, and I don’t get the same issue with a similar setup:

I’m not really sure what to suggest other than something as silly as “try refreshing or relaunching your base”.

If the spelling of each option in your Single Select list exactly matches how you’ve spelled each option in your SWITCH() formula, there doesn’t seem to be a good reason for this failure.

[kuovonne]

Try looking for a leading or trailing space in your values for the single select field. If there are any, delete them.

[Michelle_King]

Its really bizarre as the same formula i have got working for a different column its really bizarre