Skip to main content
Solved

Switch formula help

  • November 19, 2020
  • 7 replies
  • 47 views
M
Forum|alt.badge.img+9

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

Best answer by Jeremy_Oglesby

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
)

7 replies

J
Forum|alt.badge.img+18
  • Jeremy_Oglesby
  • Inspiring
  • Answer
  • November 19, 2020

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
)
ScottWorld
kuovonne
M
Forum|alt.badge.img+9
  • Michelle_King
  • Author
  • Known Participant
  • November 20, 2020

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
)

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
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 20, 2020

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)


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.

M
Forum|alt.badge.img+9
  • Michelle_King
  • Author
  • Known Participant
  • November 20, 2020

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.


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

J
Forum|alt.badge.img+18

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


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
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 20, 2020

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


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

M
Forum|alt.badge.img+9
  • Michelle_King
  • Author
  • Known Participant
  • November 20, 2020

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


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

Terms & ConditionsAccessibility statement