Excel formula/solution for grading multiple choice questions with multiple answers

Question

I have an Excel sheet where I input student test answers like so:

1. A
2. B
3. A,B
4. C,D

and compare these answers to the correct answers:

1. A
2. A
3. B
4. C,D,E

I know how to use an IF function to output 'CORRECT' in a cell if the student answer is correct (e.g. answer 1) and otherwise spit out 'INCORRECT'.

What I can't seem to do is find a formula which will spit out 'CORRECT' if correct (1); 'INCORRECT' if wrong (2); 'MISTAKE' if the student got one of the answers correct, but at the same time answered another choice incorrectly (3); and 'MISSED' if the student answered correctly but missed an answer (4).

Is there any way to do this? I've tried using wildcards and countifs but it's beyond my Excel level.

Thanks in advance for any help!

Answer 1

The solution

Lets represent the answer in binary like this, with bit order EDCBA i.e.

01010

means the answer is

B,D

To convert the text answer (student or answer sheet ) we use

=ISNUMBER(SEARCH("A", A1))*2^0 + ISNUMBER(SEARCH("B", A1))*2^1 + ISNUMBER(SEARCH("C", A1))*2^2 + ISNUMBER(SEARCH("D", A1))*2^3 + ISNUMBER(SEARCH("E", A1))*2^4

Now to compare. Lets say we have C1 the correct answer and D1 the student answer, both in "bit-wise" form using above formula. Then

=IF(C1 = D1, "Correct", IF(BITAND(BITXOR(C1,D1),C1)=C1, "Incorrect", IF(BITAND(BITXOR(C1,D1),C1)=BITXOR(C1,D1), "Missing", "Mistake")))

The explanation

I could try and explain it, but I would have to kill you and then myself. Maybe I can try... It helps to think of detecting the "mistake" as the difficult one, so leave it for the last "catch all" case of the if. Consider skipping this in favour of the image below:

    X XOR Y is a list of differences between lists X and Y
    X AND Z = X means the list Z must at least contain everything in list X
    X AND Z = Z means the list X must at least contain everything in list Z

Now lets say X is the list of correct answers (CA), Y is list of the student's answers (SA). Then:

    Z = X XOR Y is a list of differences between CA and SA

If Z = 0 then the list is empty and CA = SA i.e. "Correct", else if

    X AND Z = X 

then the list of differences must contain at least everything in list of correct answers (i.e. no correct answers = "incorrect"), else if

    X AND Z = Z 

then the list of correct answers must contain at least everything in the list of differences (i.e. no wrong answers = "missing", one or more correct), else

    NOT(all of the above)

then one or more correct answer and one or more incorrect answer = "Mistake".

TL;DR

Its actually quite easy if you draw it (insight after sleeping!):

Answer 2

You can use a formula like this:

=LET(correct_answer,XLOOKUP(A2,$I$1:$I$4,$J$1:$J$4,""),IFS(B2=correct_answer,"CORRECT",IFERROR(FIND(B2,correct_answer,1),0)>0,"MISSED",IFERROR(FIND(correct_answer,B2,1),0)>0,"MISTAKE",TRUE,"INCORRECT"))

we use LET to assign the name correct_answer to the result of the XLOOKUP to retrieve the answer from the list of correct answers. We then use IFS to make comparisons between the student's answer and the correct answer.

If you see a NAME error when using this, you likely don't have access to LET. In that case, you should remove the call to LET and replace each instance of correct_answer with a copy of the XLOOKUP function.

Answer 3

Here is another Microsoft365 alternative:

Formula in C1:

=INDEX({"Correct","Incorrect","Mistake","Missed"},MATCH(AVERAGE(UNIQUE(ISNUMBER(FIND(FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),A1)))+(A1=B1)),{2,0,1,0.5},0))