IF with multiple choices dropdown list cell

Karl Abran

Hello everyone!

English is a second language for me so please forgive my spelling mistakes in advance.

I have a form with a multiple choice dropdown that populates one of my columns in my sheet. I want to do an IF to give a rating based on the answers.

Here's what I got:

=IF([Question]@row = "answer1" and "answer2"); 0; IF([Question]@row = "answer1" and "answer3"; 0,5; IF([Question]@row = "answer1"; 1)))

I would like to add a ELSE like in VBA for the remaining combinations. Also the AND function doesn't work like that in Smartsheet, so I'm not sure how I should do it.

Thank you for your help!

Karl

Paul Newcome

My apologies. I got a little ahead of myself.

=IF(AND(HAS(Question@row; "answer1"); HAS(Question@row: "answer2")); 0; IF(AND(HAS(Question@row; "answer1"); HAS(Question@row; "answer3")); 0,5; IF([Question]@row = "answer1"; 1; "ELSE")))

Paul Newcome

You have a colon instead of a semicolon after the second "Question@row".

Paul Newcome

Try this...

=IF(AND(Question@row = "answer1"; Question@row = "answer2"); 0; IF(AND(Question@row = "answer1"; Question@row = "answer3"); 0,5; IF([Question]@row = "answer1"; 1; "ELSE")))

Karl Abran

Hi Paul,

I tried with the layout you suggested, and a few variants, and here's the result:

Always write ELSE:

=IF(AND(Question@row = "answer1"; Question@row = "answer2"); 0; IF(AND(Question@row = "answer1"; Question@row = "answer3"); 0,5; IF([Question]@row = "answer1"; 1; "ELSE")))

Write nothing (and without the ELSE):

=IF(AND(Question@row = "answer1"; Question@row = "answer2"); 0; IF(AND(Question@row = "answer1"; Question@row = "answer3"); 0,5; IF(Question@row = "answer1"; 1)))

Write #UNPARSEABLE:

=IF(AND(Question@row = "answer1"; Question@row = "answer2"); 0; IF(AND(Question@row = "answer1"; Question@row = "answer3"); 0,5; IF(Question@row = "answer1"; 1; IF(Question@row = "ELSE"; 0.75))))

Do you know how I can make it work? I think we are close but I don't have enough experience with Smartsheet to know why it won't work.

Thank you,

Karl

Paul Newcome

My apologies. I got a little ahead of myself.

=IF(AND(HAS(Question@row; "answer1"); HAS(Question@row: "answer2")); 0; IF(AND(HAS(Question@row; "answer1"); HAS(Question@row; "answer3")); 0,5; IF([Question]@row = "answer1"; 1; "ELSE")))

Karl Abran

Hi Paul, it still put me the word ELSE each time. I want to give a value when it's another answer. Here what I tried with your last comment:

=IF(AND(HAS(Question@row = "answer1"); HAS(Question@row = "answer2")); 0; IF(AND(HAS(Question@row = "answer1"); HAS(Question@row = "answer3")); 0,5; IF(Question@row = "answer1"; 1; IF(Question@row = "ELSE");0.75)))

It still won't work, (#UNPARSEABLE).

Thanks a lot for your help!

Karl

Paul Newcome

Are you updating "answer1" with the specific text that would be found in the cell? Same for "answer2" and "answer3"?

The last formula I posted should work as long as you have updated the "specific text" inside each of the HAS functions.

Additioanlly, the HAS functions should look like this:

HAS(Question@row; "answer1")

and not like this:

HAS(Question@row = "answer1")

Karl Abran

Hi Paul,

I did a simplified test to make sure everything works (see screenshot), so the specific text is "Answer1", "Answer2, etc.

I corrected the HAS(Question@row = "answer1") for HAS(Question@row; "answer1"), but it still won't work... Did I forgot something?

Thank you,

Karl

Paul Newcome

You have a colon instead of a semicolon after the second "Question@row".

Karl Abran

Thanks a lot! Everything is working fine now!

Paul Newcome

Happy to help. 👍️