Use Multi Select to Calculate Score

Introduction

Case Introduction

The logic of Multi Select is: After multiple options are ticked, the options are sorted in the order they are ticked, not in the order of the options. For example, when B, C and A are ticked in order in Multi Select, the stored order is "B, C, A" instead of "A, B, C".

Therefore, when you design exam questions by Multi Select, problems will occur if you calculate the score using the IF function: IF"(question=="Option 1, Option 2, Option 3, score,0)". Therefore, this document will introduce a method to calculate the score using the COUNT function.

Application Scenario(s)

You can use Multi Select to set questions and calculate scores.

Preview

Setting Procedure

Configuring a Form

Add a Multi Select field for the member to choose the answer, a Single Line field to store the correct answer, and a Number field to calculate the score (for example, ten points for the correct answer, zero points for otherwise).

Setting a Field to Store the Correct Answer

Set the field which stores correct answers as an auxiliary field, this field value will be used to compare with the ticked options in the formula. Go to Design > Correct Answer > Field Properties and fill in the correct answer to the question in Initial Value.

Note:

Answers should be separated by commas, and there is no fixed order of the answers.

Set this field as invisible. Then you have to set Hidden Field Default Value to Always Re-calculate in Form Properties.

Members will get the score only when their answers meet the following two conditions:

Counting Options

A correct answer should meet the first condition "The number of ticked options is the same as that of correct answers". Therefore, the COUNT function can be used to calculate the number of ticked options and the number of correct answers. You can set the formula as follows:

COUNT(Tick Answers)==COUNT(SPLIT(Correct Answer,',')

It means the number of the ticked options is the same as that of correct answers.

The SPLIT function splits the text field of the correct answers into an array with commas.

Judging Options

When the number of ticked options is the same as that of the correct answers, you also need to figure out whether the options are the same.

If the correct answers are "A, B, C" and the ticked options are "A, B, E", then they are inconsistent. Therefore, you can combine the correct answers and the ticked options together to eliminate duplication. After eliminating, the combination has four characters which are "A, B, C, E", this is inconsistent with the number of correct answers. Then the given answer is wrong.

If the ticked options are "A, B, C", then you can combine the correct answers and the ticked options together to eliminate duplication, and the number of characters in the combination (A, B, C) is three, which equals to the number of the correct answers. Then the given answer is correct.

Therefore, a correct answer should also meet the second condition: After removing the duplicate options of the ticked options and the correct answers, the number of characters in the combination should equal that of the correct answers. You can set the following formula:

COUNT(UNION(SPLIT(Correct Answer+','+Tick Answer,',')))==COUNT(SPLIT(Correct Answer,','))

It means the number of ticked options equals that of correct answers after the combination of the selected answer and the correct answer is deduplicated.

The UNION function can automatically eliminate duplicate data in the text.

Setting Score Field

To sum up, when you set the formula of Score, two conditions can be added by the IF function. The formula of the Score is:

IF(AND(COUNT(Tick Answer)==COUNT(SPLIT(Correct Answer,',')),COUNT(UNION(SPLIT(Tick Answer+','+Correct Answer,',')))==COUNT(SPLIT(Correct Answer,','))),10,0)

It means that if both the above conditions are met, the score is 10; otherwise, no score is obtained.

Note:

After the formula is copied, it cannot be used directly. You need to replace the field manually.

Note(s)

Checkbox can calculate scores using the IF function "IF(question ==" Option 1, Option 2, Option 3, score,0) ", regardless of the order in which the options are ticked.