MCQ form

I created an MCQ form that I need to evaluate. for example: 1 question, if the correct is answer is C, it can give one mark or zero mark. like this way created 5 questions, 6th column is the result. it needs to sum up the answer. Please help me.

Best Answers

  • Nick Korna
    Nick Korna Community Champion
    Answer ✓

    In this case you would change it to:

    =IF([1.Question]@row = "C",1,0)

    The [ ] brackets go around your column name that's being referenced.

  • Nick Korna
    Nick Korna Community Champion
    Answer ✓

    If you had a column for each (1Q, 2Q, etc.) arranged in the way you've started, then you would use either of:

    =1Q@row + 2Q@row + 3Q@row +4Q@row +5Q@row

    =SUM([1Q]@row, [2Q]@row, [3Q]@row, [4Q]@row, [5Q]@row)

    If you want to skip this, then this should work:

    =IF([1.Question]@row = "C", 1, 0) + IF([2.Question]@row = "B", 1, 0) + IF([3.Question]@row = "A", 1, 0) + IF([4.Question]@row = "B", 1, 0) + IF([5.Question]@row = "B", 1, 0)


Answers

  • Nick Korna
    Nick Korna Community Champion

    Hi @kumars23,

    A couple of options for you:

    You can add additional columns (to the sheet, but not the form) with an IF statement for each to show whether that person has gained a mark or not from that question. Using your example:

    =IF([Question 1 Answer]@row = "C", 1,0)

    This would give 1 mark for the correct answer of C and 0 for any other answer.

    You can them sum these columns (by row).

    Alternatively you can jump straight the total in a single column:

    =(IF([Q1 Answer]@row = "C", 1, 0)) + IF([Q2 Answer]@row = "A", 1, 0) + .....

    Hope this helps, but if you've any problems/questions then let us know. 🙂

  • Hi Nick Korna,

    Thank you for your quick reply. I attached the problem. what I have to enter in the bracket. I mean column name ? Please help.

  • Nick Korna
    Nick Korna Community Champion
    Answer ✓

    In this case you would change it to:

    =IF([1.Question]@row = "C",1,0)

    The [ ] brackets go around your column name that's being referenced.

  • It works for single-column formula. for each questions whether I need to create a single column? how can sum for all the 5 requestions? please help.


  • Nick Korna
    Nick Korna Community Champion
    Answer ✓

    If you had a column for each (1Q, 2Q, etc.) arranged in the way you've started, then you would use either of:

    =1Q@row + 2Q@row + 3Q@row +4Q@row +5Q@row

    =SUM([1Q]@row, [2Q]@row, [3Q]@row, [4Q]@row, [5Q]@row)

    If you want to skip this, then this should work:

    =IF([1.Question]@row = "C", 1, 0) + IF([2.Question]@row = "B", 1, 0) + IF([3.Question]@row = "A", 1, 0) + IF([4.Question]@row = "B", 1, 0) + IF([5.Question]@row = "B", 1, 0)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!