Skip to main content
Solved

Switch formula help

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
)
View original
Did this topic help you find an answer to your question?

7 replies

J
Forum|alt.badge.img+18
  • Jeremy_Oglesby
  • Inspiring
  • 1691 replies
  • 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
2 people like this
  • ScottWorld
    ScottWorld
  • kuovonne
    kuovonne
M
Forum|alt.badge.img+9
  • Michelle_King
  • Author
  • Known Participant
  • 31 replies
  • November 20, 2020
Jeremy_Oglesby wrote:

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+27
  • kuovonne
  • Brainy
  • 6006 replies
  • November 20, 2020
Michelle_King wrote:

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
  • 31 replies
  • November 20, 2020
kuovonne wrote:

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
Michelle_King wrote:

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+27
  • kuovonne
  • Brainy
  • 6006 replies
  • November 20, 2020
Michelle_King wrote:

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
  • 31 replies
  • November 20, 2020
kuovonne wrote:

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

Reply


Most helpful members this week

Terms & Conditions