Help

Formula for Scoring Multiple Select Field

Topic Labels: Data Formulas
164 3
cancel
Showing results for 
Search instead for 
Did you mean: 
janevezina
4 - Data Explorer
4 - Data Explorer

Hi,

I need help creating a formula for scoring a quiz that I am making in Airtable. The questions are a combination of single select and multiple select. There are 100 questions total and each question is worth 1 point if answered correctly. The multi-select questions must be answered exactly right to get 1 point, otherwise they get 0. I am running into a problem with the multiple select questions in the formula. With the way I have it written, it will count 1 point if all three of the correct selections are selected in any order. BUT if one of the incorrect selections are selected in addition to the three correct selections, it is still counting 1 point. How can I tell it that if any one of the incorrect selections are selected that the score should be 0? In this particular example A, B, & C are correct. D and E are incorrect and should not be selected for them to get the point. The highlighted area in the attached formula is the part of the formula for the multi select question. The other attachment is an image of the quiz question. In order for the user to get 1 point they need to select the three red responses.

3 Replies 3

Hm would love to help but I'm not really following I'm afraid

If you could provide a screenshot with some example data and the score you would want for each of those manually keyed in I could try to help!  The screenshot would allow me to do some pattern matching to figure out what the scoring logic is

Thank you!! I have attached another screen shot of the quiz question as it is written in a word document. I hope this helps. In order for the user to get it correct, they must select the three red response options only. My issue with the way the formula is written right now is that if they select the three red response AND one of the incorrect black responses, it will still give them a point. 

I see the new screenshot, thanks!  And thanks for the details too!  Does this look right? 

Screenshot 2024-07-16 at 11.02.12 AM.png

IF(
  AND(
    FIND(
      'Right task', Answer
    ),
    FIND(
      'Right circumstance', Answer
    ),
    FIND(
      'Right person', Answer
    ),
    FIND(
      'Right supervision', Answer
    ) = 0,
    FIND(
      'Right direction and communication', Answer
    ) = 0
  ),
  1,
  0
)

Link to base