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

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

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!

• Hi @Chilli, don't forget to checkmark one of the solutions if it worked for you! Apr 22, 2021 at 7:04

The solution

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

``````01010
``````

``````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!):

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.

• Nice answer. LET is only in Office 365 btw. Apr 20, 2021 at 18:21
• Hmm. I think there is a problem with this approach though. If the student answered A,C and the answer was A,B,C it would not pick it up correctly. I might have an alternative suggestion. Apr 20, 2021 at 18:26
• You're right - it doesn't handle that case. Apr 20, 2021 at 19:46

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))
``````
• Thats some high level excelling. Care to explain how it works? Apr 21, 2021 at 7:56
• @Mobus. The main thing here is Filterxml to split a comma-seperated item into an array to be thrown into further analysis. It's hard to further explain since I'm no longer behind a pc. Try the evaluation in Excel to see a step by step calculation. All is done to end up with 1 out of 4 unique numeric options.
– JvdV
Apr 21, 2021 at 8:50
• Not sure if Mistake is working correctly. Student A,C with correct A,B comes up as Missing. Apr 21, 2021 at 17:15
• @JvdV we share a home country! .co.za FTW Apr 22, 2021 at 7:14
• @Mobus, my other half is from SA, I myself am not but been there often. Love it.
– JvdV
Apr 22, 2021 at 7:21