Applying complex formula to Column

Dan W.

Good day gurus,

I need to apply multiple IF statements to a Column. Based on the choice made from a drop down menu, the result will return a number. The statement below works; however is not in the correct syntax for applying it to an entire column. I tried to use @Row but I am failing miserable as all my attempts are resulting in #UNPARSEABLE return. Any assistance would be great!

=IF(DROPDOWN1 = "Very unlikely", 1, IF(DROPDOWN1 ="Unlikely", 2, IF(DROPDOWN1 ="Possible", 4, IF(DROPDOWN1 ="Likely", 8, IF(DROPDOWN1 = "Very likely", 16, "")))))

"NOTE: SmartSheet auto formats the field and removes the brackets [ ]. My goal is to automatically apply it to all new records in the sheet.

Thanks!

Dan W.

[SOLVED]

Seems as if SS did not like me typing my formula in. It works fine if I type "IF(" and then manually clicking on the field its referencing. I did this manually for each "IF(" statement and it took with no issues. Here is the correct statement for those who need to do something similar:

=IF(DROPDOWN@row = "VERY UNLIKELY", 1, IF(DROPDOWN@row = "UNLIKELY", 2, IF(DROPDOWN@row = "POSSIBLE", 4, IF(DROPDOWN@row = "LIKELY", 8, IF(DROPDOWN@row = "VERY LIKELY", 16, "")))))

David Joyeuse

Hi @Dan W.

You probably got #UNPARSEABLE because you added @row to DROPDOWN1.

Instead @row is supposed to replace the 1

So you should have:

=IF(DROPDOWN@row = "Very unlikely", 1, IF(DROPDOWN@row ="Unlikely", 2, IF(DROPDOWN@row ="Possible", 4, IF(DROPDOWN@row ="Likely", 8, IF(DROPDOWN@row = "Very likely", 16, "")))))

Hope it helped!

Dan W.

Thanks for the quick reply David.

Unfortunately when I replace it with the following code, I still get #UNPARSEABLE error:

=IF(DROPDOWN@Row = "Very unlikely", 1, IF(DROPDOWN@Row = "Unlikely", 2, IF(DROPDOWN@Row = "Possible", 4, IF(DROPDOWN@Row = "Likely", 8, IF(DROPDOWN@Row = "Very likely", 16, "")))))

Dan W.

So after testing David's suggestion it does in fact work for a single IF statement.

=IF(DROPDOWN@row = "VERY UNLIKELY", 1, "")

I just need to combine the other statements with out getting the error.

Dan W.

[SOLVED]

Seems as if SS did not like me typing my formula in. It works fine if I type "IF(" and then manually clicking on the field its referencing. I did this manually for each "IF(" statement and it took with no issues. Here is the correct statement for those who need to do something similar:

=IF(DROPDOWN@row = "VERY UNLIKELY", 1, IF(DROPDOWN@row = "UNLIKELY", 2, IF(DROPDOWN@row = "POSSIBLE", 4, IF(DROPDOWN@row = "LIKELY", 8, IF(DROPDOWN@row = "VERY LIKELY", 16, "")))))