I am automating the comments for some test marking, and I wanted to give a bit of variety to the comments I give for correct answers, so I have used the following formula:

=IF(E5=1,CHOOSE(RANDBETWEEN(1,4),"Great work!","Excellent work!","Great job!","Well done!"),"")

Here, if the answer is correct, then cell E5 is set to 1, and it chooses from one of 4 comments and leaves an empty string otherwise.

This works fine, but the test has two questions, and I would prefer it if when both questions are correct, they don't receive the same comment.

For example, if the formula above is in cell E11 and E16=1 indicates question 2 is correct, then I would like to do something along the lines of:

=IF(E16=1,IF(E11="",CHOOSE(RANDBETWEEN(1,4),"Great work!","Excellent work!","Great job!","Well done!"),CHOOSE(RANDBETWEEN(1,3),[set difference of original strings and string in E11])),"")

In plain english: Given question 2 is correct, but question 1 is not, to determine the comment for question 2 just select a random comment from the list; however, if both questions are correct, then the comment for question 2 must be randomly selected from the original list, not including the comment that was given for question 1 (no duplicates).

..but I'm not sure how to make that work in Excel.

EDIT:

I know I could just have two disjoint lists of "correct" comments for each question so there would never be any duplicates, but I am just curious as to whether it is possible to do it this way using Excel or not.

CodePudding user response：

If you have Excel 365 you can just filter out the phrase which has already been used:

=LET(congrats,{"Great work!","Excellent work!","Great job!","Well done!"},
filterCongrats,FILTER(congrats,congrats<>E11),
IF(E16=1,INDEX(filterCongrats,RANDBETWEEN(1,COUNTA(filterCongrats))),""))