Highlighting Inconsistencies

[62deadfly]

Hi all.

First time posting here, so I apologise if this has gone in the wrong forum.

I'm trying to enter a lookup formula (or something similar) to highlight for me whether there are any inconsistencies in marking an exam. To make it more difficult, I'm not able to upload a sample spreadsheet from here, so I'll do my best to describe it.

I have a pool of 9 teachers, and 5 students. The students take a test of 5 questions, all Yes or No answers. This test is then marked by a teacher (selected at random), then again by another, then once more by a third.

• A3:A17 has a list of names of teacher's names assigned to mark the test in that row.
• B3:B17 has a list of pupil names, however as each test is marked 3 times, Student A appears is cells B3:B5, Student 2 B6:B8 etc
• C3:G17 contains fields where the teacher marks questions 1-5, either right or wrong

To underline an example, Student 1 has his test marked by Teacher A, Teacher B and Teacher C. Teacher A & B both marked him identically, however Teacher C marked Question 3 correct when A & B marked incorrect, creating an inconsistency in the marking.


What I'm looking to have out of this, is for me to be able to select from a dropdown list the names of Students 1-5 (, and for any potential inconsistencies over the three markings to be shown, as well as the name of the Teacher whose marking brought about the inconsistency.

What/how can I make it happen?

Cheers.

[Andy Pope]

Select range C3:G17 and apply conditional formatting Formula Is

=SUMPRODUCT(($B$3:$B$17=$B3)*(C$3:C$17=C3))=1

Format pattern to Red.

This will simple highlight the cell with the unique result for each student.

You can add a helper cell and do student selected CF

[62deadfly]

Thank you, that's worked well.

One more query, suppose at the end (col H & I), I included the below:

(row 3 example) =COUNTIF($C3:$G3,"Yes"), and then =COUNTIF($C3:$G3,"No").

What formula would I need to enter on conditional formatting for that to be highlighted in the same manner?

[Andy Pope]

For the range H3:I17 use

=SUMPRODUCT(($B$3:$B$17=$B3)*(H$3:H$17=H3))=1