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