Data validation for multiselect dropdown lists?

I am embedding a Smartsheet form in an online training module, to collect and validate the answers in the absence of an LMS. For a single dropdown, I found it easy - I create a question column, in which I collect the answer, and an answer column, in which I validate the answer. To validate the answer I use a simple IF formula, such as:

=IF([field]="correct answer", 1,0). This returns a value of 1 if the answer is correct and 0 if it is incorrect, and the values go in a formula that calculates the percentage of correct answers.

But I had some questions that were multi-select dropdowns. If two answers are correct, what is the correct syntax for an IF/AND to return a value of 1 if and only if both correct answers have been selected?

(I found a workaround, but is not elegant. I revert to a single select and label the questions with A, B, C, etc, and then create a D answer that says "Both A and C are correct". Selecting D returns the value 1, so I can work with this, but I want an elegant solution that works for a multi-select dropdown.

Did anybody found a solution to this issue?

Best Answer

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Karoly Ban Matei

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Karoly Ban Matei ,


    Using AND(CONTAINS( may be your best bet. Try something like this:

    =IF(AND(CONTAINS("CorrectAnswer1", Field@row), CONTAINS("CorrectAnswer2", Field@row)), 1, 0)


    Hope this helps!


    Best,

    Heather

  • Karoly Ban Matei
    Karoly Ban Matei ✭✭

    Thanks, @Heather D , it works!

    It does have a small drawback - as long as the two correct answers are selected, it always returns a correct value, even if a third option is selected.

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    @Karoly Ban Matei

    How about this, assuming there are only 4 options in the question:

    =IF(OR(CONTAINS("WrongAnswer1", Field@row), CONTAINS("WrongAnswer2", Field@row)), 0, IF(AND(CONTAINS("CorrectAnswer1", Field@row), CONTAINS("CorrectAnswer2", Field@row)), 1, 0))


    This basically says they will get 0 points if either of the wrong answers is selected; otherwise, they will get 1 point if both correct answers are selected; otherwise, they will get 0 points.

  • Karoly Ban Matei
    Karoly Ban Matei ✭✭

    Thanks, @Heather D, that works! I get the logic behind it, but being 20 years removed from any programming the syntax eluded me.

    Thanks again,

    Karoly

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Karoly Ban Matei

    Glad it worked! It took me a hot minute to get it figured out just right. 😊 Have a great week.